SEPARATE STRING VALUE WITH DELIMETER AND INSERT

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SEPARATE STRING VALUE WITH DELIMETER AND INSERT

Author

Topic

paritosh
Starting Member

42 Posts

Posted - 2012-07-04 : 10:11:40

HI ALL

I HAVE A STRING WITH DELIMETER

STRING IS 1^2~3^4~5^6

TREAT ^ AS COLUMN SEPARATOR AND ~ AS ROW SEPARATOR

HOW TO HANDLE THIS TYPE OF STRING AND DATA INSERT IN TABLE

WHEN STRING = 1^2~3^4~5^6
THEN INSERT DATA IN TABLE AS AFETR INSERT DATA ON TABLE :-

COL1 COL2
1 2
3 4
5 6

WHEN STRING HAVE N LENGTH
ASSUME STRING = 1^2~3^4~5^6........N

THEN INSERT IN TABLE AND SHOW DATA AS

COL1 COL2
1 2
3 4
5 6
.. ..
.. ..
.. ..
.. ..
N N

HOW TO MANAGE THIS PROBLEM

THANKS IN ADVANCE ...

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-04 : 10:19:36

Is it always two columns? You will need to use a string-splitter to split the string into individual tokens and then insert. There are a number of different ways to do the string splits. The one I use most often is the one in Fig. 21 in Jeff Moden's article here: http://www.sqlservercentral.com/articles/Tally+Table/72993/ Copy and run the script to install the function.

Once you have the function installed, you would do something like this:

DECLARE @x VARCHAR(32) = '1^2~3^4~5^6';
INSERT INTO YourTable (col1, col2)
SELECT
	LEFT(Item,CHARINDEX('^',Item)-1),
	STUFF(Item,1,CHARINDEX('^',Item),'')
FROM
	dbo.DelimitedSplit8K(@x,'~') dsk

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-04 : 10:27:25

declare @str varchar(20) = '1^2~3^4~5^6'

;with cte as
(
select i=charindex('^',@str+ '~'), j=charindex('~',@str+ '~',charindex('^',@str+ '~')+1)
union all
select i=charindex('^',@str+ '~',i+1), j=charindex('~',@str+ '~',charindex('^',@str+ '~',i+1)+1) from cte where charindex('^',@str+ '~',i+1) <> 0
)
insert tbl
select SUBSTRING(@str, i-1, j-i-1)
from cte

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-04 : 17:23:48

you may be better off sending the values in xml format with node elements for each column and then node themselves representing rows. that will make it clear on what exact value a particular element is having and also if some of involved column values are optional. then inside the code you can use xml functions like nodes(),value() etc to shred the data

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources