SQL String Parsing - SQL Server Forums

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

SQL String Parsing

Author

Topic

cshah1
Constraint Violating Yak Guru

347 Posts

Posted - 2008-04-18 : 15:03:26

I have a string that is coming from a legacy system

###T1937###U1875###U1960###U3287###U5926###U6388###U4408###T1909###U2620###U5025###U6354###U7072###U7074###U6715###U6714###U4085###U6441###U7067###U7073###U2392###U6348###U7758###U6717###U7755###U7069###U7756###U6350###U6760###U7070###D0002###D0001###U6238###U6349###U6353###U6355###F0005###U7750###U6351###U7757###

How can I convert above string to comma separted values

like one below so that it can be used for IN Clause for my SQL

'T1937','U1875','U1960','U3287','U5926','U6388','U4408','T1909','U2620','U5025','U6354','U7072','U7074','U6715','U6714','U4085','U6441','U7067','U7073','U2392','U6348','U7758','U6717','U7755','U7069','U7756','U6350','U6760','U7070','D0002','D0001','U6238','U6349','U6353','U6355','F0005','U7750','U6351','U7757'

Thanks in advance

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-18 : 15:05:04

Just use the CSV function and pass the ### as the delimiter. Search this site for the CSV function.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-18 : 15:08:52

SELECT '''' + REPLACE(YourString,'###',''',''') + ''''

assuming delimiter(###) is consistent throughout

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-18 : 15:12:04

You can't use that for the IN clause though as was requested.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

cshah1
Constraint Violating Yak Guru

347 Posts

Posted - 2008-04-18 : 15:13:53

Tara, thanks for your speedy reply

thanks for waking me up soemthing from SQLMag
http://www.sqlmag.com/Article/ArticleID/21071/sql_server_21071.html

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-18 : 15:15:37

quote:
Originally posted by tkizer

You can't use that for the IN clause though as was requested.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Yup thats true Tara unless you use d-sql.sorry missed it.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-18 : 16:40:47

Why not these more efficient splir functions?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

Or these?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

E 12°55'05.25"
N 56°04'39.16"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-18 : 16:49:12

Thanks Peso. That's what I was referring to when I mentioned the CSV function, just couldn't remember that "split" was the term to use.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-18 : 16:52:56

No worries!

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-19 : 02:55:52

Also make sure you read these articles as well
http://www.sommarskog.se/arrays-in-sql.html

Madhivanan

Failing to plan is Planning to fail

cshah1
Constraint Violating Yak Guru

347 Posts

Posted - 2008-04-21 : 09:50:10

All, I found several version of split or parse csv function on SQLteam. It is quite overwhelming and I saw one from Nigel R which I kind of liked it.

Any recommendation in terms of speed based on the string provided in my first post.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-21 : 09:53:13

Read the second link I posted.
There are speed comparisons.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-21 : 09:55:23

Is the string ALWAYS


###   Three # as delimiter
X     One alphanumeric character
0000  Four numeric digits

and then repeated and finally a closing ### delimiter?

E 12°55'05.25"
N 56°04'39.16"

cshah1
Constraint Violating Yak Guru

347 Posts

Posted - 2008-04-21 : 09:57:26

Yes
like one below
'###U5025###U6354###U7072###U7074###U6441###U7070###U6238###U6353###'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-21 : 09:58:32

[code]DECLARE @Sample VARCHAR(8000)

SET @Sample = '###T1937###U1875###U1960###U3287###U5926###U6388###U4408###T1909###U2620###U5025###U6354###U7072###U7074###U6715###U6714###U4085###U6441###U7067###U7073###U2392###U6348###U7758###U6717###U7755###U7069###U7756###U6350###U6760###U7070###D0002###D0001###U6238###U6349###U6353###U6355###F0005###U7750###U6351###U7757###'

SELECT SUBSTRING(@Sample, 8 * Number - 4, 5)
FROM master..spt_values
WHERE Type = 'p'
AND Number BETWEEN 1 AND (DATALENGTH(@Sample) - 3) / 8[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-21 : 10:00:38

[code]DECLARE @Sample VARCHAR(8000)

SET @Sample = '###T1937###U1875###U1960###U3287###U5926###U6388###U4408###T1909###U2620###U5025###U6354###U7072###U7074###U6715###U6714###U4085###U6441###U7067###U7073###U2392###U6348###U7758###U6717###U7755###U7069###U7756###U6350###U6760###U7070###D0002###D0001###U6238###U6349###U6353###U6355###F0005###U7750###U6351###U7757###'

SET @Sample = REPLACE(@Sample, '###', ''',''')

IF @Sample LIKE ''',%'
SET @SAMPLE = SUBSTRING(@Sample, 3, 8000)

IF @Sample LIKE '%,'''
SET @SAMPLE = LEFT(@Sample, DATALENGTH(@Sample) - 2)

PRINT @Sample[/code]

E 12°55'05.25"
N 56°04'39.16"

cshah1
Constraint Violating Yak Guru

347 Posts

Posted - 2008-04-21 : 10:01:36

Peso,

You just amazed me. I been doing this for 8 years from SQL Server to .NET and Java but at the end of day I feel I know nothing.

Thank you.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-21 : 10:06:02

This 04/21/2008 : 09:58:32
or this 04/21/2008 : 10:00:38 ?

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources