SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Parse complicated multivalue field
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dplaut
Starting Member

7 Posts

Posted - 07/25/2013 :  12:12:34  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3557 Posts

Posted - 07/25/2013 :  13:09:57  Show Profile  Reply with Quote
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;
Go to Top of Page

dplaut
Starting Member

7 Posts

Posted - 07/25/2013 :  15:09:58  Show Profile  Reply with Quote
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,"],","]|"),"),",")|")
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 07/26/2013 :  00:19:32  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000