Use of CharIndex in SQL

Self-innovator
Posted by Self-innovator under Sql Server category on | Points: 40 | Views : 2062
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

Comments or Responses

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!
Posted by: Self-Innovator on: 2/20/2012 Level:Bronze | Status: [Member] | Points: 10
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

Login to post response