SQL Code for multiple delimiters in given string


CREATE TABLE #tableAll (InputString NVARCHAR(20))

CREATE TABLE #tableAllSep(ContactName NVARCHAR(50),RoleName NVARCHAR(50))

DECLARE @InputIds NVARCHAR(2000)

SELECT @InputIds='Ram@NewYaork,Raj@India,Yuvi@Aus'

DECLARE @SQL NVARCHAR(2000)

SELECT @SQL=''

SELECT @SQL ='INSERT INTO #tableAll SELECT '+''''+REPLACE(@InputIds,',',''' UNION ALL SELECT ''' )

SELECT @SQL = @SQL+''''

EXEC (@SQL)



--SELECT * FROM #tableAll

INSERT INTO #tableAllSep

SELECT LTRIM(RTRIM(LEFT(InputString,CHARINDEX('@', InputString)-1))),



SUBSTRING(InputString,CHARINDEX('@', InputString)+1,

LEN(InputString)-LEN(LEFT(InputString,CHARINDEX('@', InputString)-1))) FROM #tableAll

SELECT * FROM #tableAllSep

DROP TABLE #tableAll

DROP TABLE #tableAllSep