How to split the Concatenated string with special characters in SQL
DECLARE @Test VARCHAR(1000), @RecSet VARCHAR(500), @ID VARCHAR(10), @Value VARCHAR(60)
DECLARE @StartingPos INT,@RecPos INT
SET @StartingPos= 1
SET @Test = '123,as±2345,ddd±46465,sdsd±'
WHILE @StartingPos<=LEN(@Test)
BEGIN
SELECT @RecPos = CHARINDEX('±',@Test,@StartingPos)
SELECT @RecSet=SUBSTRING(@Test,@StartingPos,@RecPos-@StartingPos)
PRINT @RecSet
SELECT @ID=SUBSTRING(@RecSet,1,CHARINDEX(',',@RecSet,1)-1)
SELECT @Value=SUBSTRING(@RecSet,LEN(@ID)+2,LEN(@RecSet)-LEN(@ID))
PRINT @ID
PRINT @Value
SELECT @StartingPos=@RecPos+1
END
Posted by:
PandianS
on: 1/30/2012
Level:Silver | Status: [Member] [MVP] | Points: 10
Hi
Nice One.
But, Did you check whether the test data ends with "," (comma) or Not ends with special character... will work fine / not ?
The following test data will not work properly according to your script!
SET @Test = '123,as±2345,ddd±46465,sdsd,'
or
SET @Test = '123,as±2345,ddd±46465,sdsd'
Kindly test the code once before you post the same!
The above Code i've Posted is Splitting the string Based on Special Character(±) and From that Substring It seperates with Comma....Try it u'll be understood