Extracting string after and before a character '-' or '<'

jaymin sathavara

6y762SQL

Hello Everyone!!

Please Review Following table.

UDF_WorkLocation_6

-------------------------------------------------

MUMBAI - Maharastra<6>

AHMEDABAD - Gujarat<1>

MUMBAI - Maharastra<6>

CHANDIGARH - Chandigarh<34>

NULL

DELHI - Delhi<42>

NULL

INDORE - Madhya Pradesh<56>

Here it is column name 'UDF_WorkLocation_6' in table.I want split string from '-' then set into two columns And In second column Remove string after charter '<' using SQL Query. I want output in two columns as below.

Output Table
Column1	Column2
MUMBAI	Maharastra
AHMEDABAD	Gujarat
MUMBAI	Maharastra
CHANDIGARH	Chandigarh
NULL	NULL
DELHI	Delhi
NULL	NULL

Can anyone please help me?

What I have tried:

I have prepared query for split string.For instant, string is 'MUMBAI - Maharastra<6> '

and got output for second column like 'Maharastra<6>' using below sql query

SELECT LTRIM(SUBSTRING(AccountMasterUDF.UDF_WorkLocation_6, CHARINDEX('-', AccountMasterUDF.UDF_WorkLocation_6) + 2, 100))

But i also want two remove charters after '<' this.

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sachin Singh6y ago
Accepted answer
okk i found the solution, it was very simpleactually
SELECT REVERSE(PARSENAME(REPLACE(REVERSE(LEFT('MUMBAI - Maharastra<6>', CHARINDEX('<', 'MUMBAI - Maharastra<6>') - 1)), '-', '.'), 1)) AS [Town]
, REVERSE(PARSENAME(REPLACE(REVERSE(LEFT('MUMBAI - Maharastra<6>', CHARINDEX('<', 'MUMBAI - Maharastra<6>') - 1)), '-', '.'), 2)) AS [State]
Note:- replace string with UDF_WorkLocation_6 column name
+6
jaymin sathavara6y ago
I also found solution,

;with cte as
(
SELECT
RTRIM(SUBSTRING(UDF_WorkLocation_6,1, CHARINDEX('-', UDF_WorkLocation_6) - 1)) as Column1,
LTRIM(SUBSTRING(UDF_WorkLocation_6, CHARINDEX('-', UDF_WorkLocation_6) + 1, len(UDF_WorkLocation_6))) as Column1
from AccountMasterUDF
)
select Column1, SUBSTRING(Column2, 1, CHARINDEX('<',Column2) - 1) as newrighthand
from cte;
+3
jaymin sathavara6y ago
Please look into query. I want bifurcate Town of Posting column into Column1 data as 'Town of Posting' and Column2 data as State of Posting'.

For instant, now in table Town of Posting column has 'MUMBAI - Maharastra<6> ' string now i want output like 'MUMBAI' as Town of Posting column and Maharastra as 'State of Posting' in SQL server 2008.
select
AccountMaster.AccountName as 'First Name'
,'' as 'Last Name'
,'' as 'Title (Mr/Mrs/Ms/Dr)'
,'SALES' as 'Person Type (SALES / COLLECTION / MANAGEMENT)'
,AccountMaster.AccountCode as 'Employee Number'
,AccountMaster.Phone1 as 'Mobile Number'
,AccountMaster.EMail as 'Email ID'
,AccountMasterUDF.UDF_WorkLocation_6 as 'Town of Posting'
,'' as 'State of Posting'
from
AccountMaster
inner join AccountMasterEmployeeDetails on AccountMaster.AccountMasterId = AccountMasterEmployeeDetails.AccountMasterId
left join AccountMasterUDF on AccountMaster.AccountMasterId = AccountMasterUDF.AccountMasterId
left join
(select AccountMasterId,AccountName,EMail from AccountMaster
) ReportAccount ON ReportAccount.AccountMasterId = AccountMaster.ReportingToAccountMasterId
where AccountMaster.AccountIdentification = 'E'
and AccountMasterEmployeeDetails.EmployeeDepartment like '%Marketing%'
and DeActivated = 0
+2
Sachin Singh6y ago
string_Split is built-in function so whats wrong using it?
+2
jaymin sathavara6y ago
Dear Sachin Singh,
Please try to prepare query without using fuctionThank You For Support!!
+2
Sachin Singh6y ago
use below query
SELECT STRING_SPLIT (LEFT('MUMBAI - Maharastra<6>', CHARINDEX('<', 'MUMBAI - Maharastra<6>') - 1),'-')
if String_Split() doesn't work means you have sql server <12 , so you need to create a Split() function like below
Create FUNCTION [dbo].[fn_split_string]
(
    @string    nvarchar(max),
    @delimiter nvarchar(max)
)
/*
    The same as STRING_SPLIT for compatibility level < 130
    https://docs.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver15
*/
RETURNS TABLE AS RETURN
(
    SELECT
      --ROW_NUMBER ( ) over(order by (select 0))                            AS id     --  intuitive, but not correect
        Split.a.value('let $n := . return count(../*[. << $n]) + 1', 'int') AS id
      , Split.a.value('.', 'NVARCHAR(MAX)')                                 AS value
    FROM
    (
        SELECT CAST(''+REPLACE(@string, @delimiter, '')+'' AS XML) AS String
    ) AS a
    CROSS APPLY String.nodes('/X') AS Split(a)
)




SELECT dbo.fn_split_string(LEFT('MUMBAI - Maharastra<6>', CHARINDEX('<', 'MUMBAI - Maharastra<6>') - 1),'-')
+2