Parse Text Separeted By Semicolon

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

SQL Server 2008 Forums

Transact-SQL (2008)

Parse Text Separeted By Semicolon

Author

Topic

gad
Starting Member

14 Posts

Posted - 2010-10-06 : 09:52:52

Hello,
Trying to parse out text stored in one field that is separated by ";". I also need to bring along the record identifier into the results. Here is an example:

-- from --
casenum reason
00035145 No longer needs service;Other (Specify in desc)
00038323 No longer needs service;Other (Specify in desc)
00035562 CC Decline;No longer needs service

-- into --

casenum reason
00035145 No longer needs service
00035145 Other (Specify in desc)
etc..

Thank you,
Gad

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-10-06 : 10:33:07

got a tally table?

here's a really quick way to do it using a tally table


DECLARE @foo TABLE (
	[fooID] INT PRIMARY KEY
	, [txtData] VARCHAR(8000)
	)

INSERT @foo ([fooID], [txtData])
      SELECT 1, 'foo;bar;woo;shoo'
UNION SELECT 2, 'crew;slough'


SELECT
	f.[fooID]
	, SUBSTRING(f.[txtData], N + 1, CHARINDEX(';', f.[txtData], N + 1 ) - N - 1)
FROM
	(
		SELECT
			[fooID]
			, ';' + [txtData] + ';' AS [txtData]
		FROM
			@foo
	)
	AS f
	JOIN tally AS t ON t.N < LEN(f.[txtData])
WHERE
	SUBSTRING(f.[txtData], N, 1) = ';'

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-10-06 : 10:34:28

Tally table is just a table of numbers (with a clustered index on the number column). You can do lots of nifty things with them,

See here:
http://www.sqlservercentral.com/articles/T-SQL/62867/

It's worth making a free account just for this article.

FOr example my scripts results are:


fooID	(No column name)
1	foo
1	bar
1	woo
1	shoo
2	crew
2	slough

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-10-06 : 11:06:15

though there are also slower individual row parsers out there.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources