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

Question

Hello Everyone!! Please Review Following table. UDF_WorkLocation_6 ------------------------------------------------- MUMBAI - Maharastra AHMEDABAD - Gujarat MUMBAI - Maharastra CHANDIGARH - Chandigarh NULL DELHI - Delhi NULL INDORE - Madhya Pradesh 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 ' 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 ' and got output for second column like 'Maharastra ' 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 '

Sachin Singh · Answer

okk i found the solution, it was very simpleactually SELECT REVERSE(PARSENAME( REPLACE (REVERSE( LEFT ( 'MUMBAI - Maharastra ' , CHARINDEX( ' , 'MUMBAI - Maharastra ' ) - 1)), '-' , '.' ), 1)) AS [Town] , REVERSE(PARSENAME( REPLACE (REVERSE( LEFT ( 'MUMBAI - Maharastra ' , CHARINDEX( ' , 'MUMBAI - Maharastra ' ) - 1)), '-' , '.' ), 2)) AS [State] Note:- replace string with UDF_WorkLocation_6 column name

jaymin sathavara · Answer

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(' from cte;

Sachin Singh · Answer

use below query SELECT STRING_SPLIT ( LEFT ( 'MUMBAI - Maharastra ' , CHARINDEX( ' , 'MUMBAI - Maharastra ' ) - 1), '-' ) if String_Split() doesn't work means you have sql server Create FUNCTION [dbo].[fn_split_string] ( @string nvarchar( max ), @delimiter nvarchar( max ) ) /* The same as STRING_SPLIT for compatibility level 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(../*[. , '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 ' , CHARINDEX( ' , 'MUMBAI - Maharastra ' ) - 1), '-' )

jaymin sathavara · Answer

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 ' 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

Sachin Singh · Answer

string_Split is built-in function so whats wrong using it?

jaymin sathavara · Answer

Dear Sachin Singh, Please try to prepare query without using fuctionThank You For Support!!

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

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