Parse complicated multivalue field

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 complicated multivalue field

Author

Topic

dplaut
Starting Member

7 Posts

Posted - 2013-07-25 : 12:12:34

Any suggestions on how to parse this data which is in one field.

Example data:
GLAUCOMA [130022], (evaluation for glaucoma, referred in by Dr x), SORE THROAT [82]

There are two types of records in this field. the ALL CAPS records with the brackets are items selected from a list. The text within parentheses is free text typed in by user. Note that there can be a comma in the free text so I can't just use a comma as the delimiter.

Need to denormalize to:
GLAUCOMA [130022]
(evaluation for glaucoma, referred in by Dr x)
SORE THROAT [82]

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-25 : 13:09:57

If you have the option to do so, this would be best done using a Regex parser such as in C#. But if you must do it in T-SQL, here is something to get you started. This is not a complete solution - it will not work correctly if you had multiple bracket-escaped items; but that can be fixed with some effort. As you can see, it is a little bit convoluted, and I disown it - i.e., no implied warranties or fitness for a particular purpose legalese

DECLARE @x VARCHAR(255) = 'GLAUCOMA [130022], (evaluation for glaucoma, referred in by Dr x), SORE THROAT [82]'

;WITH cte1 AS
(
	SELECT
		LTRIM(RTRIM(Item)) AS Item
	FROM
		dbo.DelimitedSplit8K(@x,',')
),
cte2(c) AS
(
	SELECT Item AS [data()] FROM cte1 WHERE item LIKE '(%' OR item LIKE '%)' FOR XML PATH('')
)
SELECT Item FROM cte1 WHERE item NOT LIKE '(%' AND item NOT LIKE '%)'
UNION ALL
SELECT * FROM cte2;

dplaut
Starting Member

7 Posts

Posted - 2013-07-25 : 15:09:58

Thanks.

For now I'm going to go with this. Hoping this will catch all commas that matter and replace w/ |. Then I can split it out.

REPLACE(REPLACE(VisitReason,"],","]|"),"),",")|")

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-26 : 00:19:32

In case you want to use earlier posted solution, the UDF DelimitedSplit8K can be found here

http://www.sqlservercentral.com/articles/Tally+Table/72993/

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources