splitting commas in a string

priya priya

17y4.2k

If i give a value like this

'11,12|13,14|15,16'

I want to remove that ',' and '|' and i need my output as follows:

id value1 value2
1     11        12
2     13        14
3     15        16

I want to achieve this by inserting values in temporary table and by using charindex in sql server
thanks in advance

3 Replies

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

Sanjay Chauhan17y ago
Accepted answer
Hi

Try this:

CREATE TABLE #TempKeywords(

Value1 int,

Value2 int )

DECLARE @Keywords VARCHAR(50)

SET @Keywords='11,12|13,14|15,16'

While (Charindex('|',@Keywords)>0)

Begin

Insert Into #TempKeywords ([Value1],[Value2]) VALUES( ltrim(rtrim(Substring(@Keywords,1,Charindex(',',@Keywords)-1))), ltrim(rtrim(Substring(@Keywords,4,Charindex(',',@Keywords)-1))))

Set @Keywords = Substring(@Keywords,Charindex('|',@Keywords)+1,len(@Keywords))

End

Insert Into #TempKeywords ([Value1],[Value2]) VALUES( ltrim(rtrim(Substring(@Keywords,1,Charindex(',',@Keywords)-1))), ltrim(rtrim(Substring(@Keywords,4,Charindex(',',@Keywords)-1))))

SELECT * FROM [#TempKeywords]
Meetu Choudhary17y ago
from where you are getting this string. even you can do this in front end (.net) by using stringbuilder's split function or replace function and put them into the datatable. or in SQL then please let me knw where and how you are getting this string.
Sreekanth17y ago
i thnk ur tryng for bulk insert... with fieldterminator. , or |...