Finding elements in a column separated by ";"

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)

Finding elements in a column separated by ";"

Author

Topic

mikedu
Starting Member

9 Posts

Posted - 2012-01-09 : 14:13:02

I need to pick out elements in a column based on another column. The column is text separated by ";" (i.e. "Medical group app;Medical individual app") I want to be able to depending on another column if it contains 'group', then pull the element "Medical group app", or if it contains 'ind', then pull the element "Medical individual app". I am not sure of the best way to go about it?

SELECT ,[DIV]
,[Site_Mnem]
,[Phys_Name]
,pntype = CASE
WHEN [Edit1Txt] LIKE '%GROUP%' THEN 'Group'
WHEN [Edit1Txt] LIKE '%IND%' THEN 'Ind'
ELSE NULL
END
,appname (this is the column that contains the ';' separated values...
FROM generic table

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-09 : 14:45:28

SELECT * FROM myTable WHERE [Edit1Txt] LIKE '%GROUP%'
UNION
SELECT * FROM myTable WHERE [Edit1Txt] LIKE '%IND%'

????

Not too clear about what you exactly want

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

mikedu
Starting Member

9 Posts

Posted - 2012-01-09 : 14:52:46

Here is a sample table:

edit1txt appname mycolumn
group Medical Group app; Medical Individual app Medical Group app
group Medical Group app; Medical Indivudual app Medical Group app
individual Medical Individual app; Medical Group app Medical Individual app

I want mycolumn's logic to be if edit1txt is group, then it picks the correct element in appname

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-09 : 15:17:43

You need to supply REAL examples

And Also..please show what the expect results are SUPPOSE to look like

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

mikedu
Starting Member

9 Posts

Posted - 2012-01-09 : 15:35:12

I did, those are real examples in the reply. If the edit1txt has group, then the logic should be to get the group app name (they change based on state). Or if edit1txt has individual, then the logic should be to get the ind app name.

Here are some other examples..

edit1txt appname mycolumn
"group" "Tx Medical Group app; Tx Medical Individual app" "Tx Medical Group app"
"group" "VA Medical Group app; VA Medical Individual app" "VA Medical Group app"
"individual" "OH Medical Individual app; OH Medical Group app" "Medical Individual app"

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-09 : 17:03:45

Can you post what you expect as the result set?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-09 : 17:44:51

I KNOW there are easier ways to do this, especially if you have a string splitter function in your database, but here is one way that would work. (If you don't have a splitter function, take a look at this page - it is long, but the function is in Fig. 21. Copy and install it on your server; it is useful for a variety of tasks you may want to do in the future)

-- Set up test data
CREATE TABLE #tmp(edit1txt VARCHAR(32), appname VARCHAR(255));
GO

INSERT INTO #tmp VALUES
('group','Tx Medical Group app; Tx Medical Individual app'),
('group','VA Medical Group app; VA Medical Individual app'),
('individual','OH Medical Individual app; OH Medical Group app')

GO

-- Query
;WITH cte1 AS
(
	SELECT
		ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS RN,
		*
	FROM #tmp
),
cte2 AS 
(
	SELECT
		c.*,
		row_number() over (order by Number) as Number,
		NullIf(SubString(';' + appname + ';' , Number , CharIndex(';' , ';' + appname + ';' , Number) - Number) , '') AS data 
	FROM 
		MASTER..spt_values s
		CROSS JOIN cte1 c
	WHERE Number <= Len(';' + appname + ';') AND SubString(';' + appname + ';' , Number - 1, 1) = ';' 
	AND CharIndex(';' , ';' + appname + ';' , Number) - Number > 0
	AND [type]='P'
)
SELECT
	edit1txt,
	appname,
	data
FROM 
	cte2 
WHERE DATA LIKE '%'+edit1txt+'%';
GO

-- Cleanup
DROP TABLE #tmp;

FischMan2
Yak Posting Veteran

59 Posts

Posted - 2012-01-09 : 18:01:11

quote:
Originally posted by sunitabeck

I KNOW there are easier ways to do this, especially if you have a string splitter function in your database, but here is one way that would work. (If you don't have a splitter function, take a look at this page - it is long, but the function is in Fig. 21. Copy and install it on your server; it is useful for a variety of tasks you may want to do in the future

That page requires registration to even see it. Any chance of just posting the code here?

FischMan

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-09 : 19:23:32

I don't know the etiquette of copying a code from someone's posting. However, the code I posted does not require the code from Sql Server Central.

If you would be doing a lot of work with SQL it might not be a bad idea to register at SQL Server Central. They don't spam you or divulge your e-mail based on my experience.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-01-09 : 19:30:40

using Peso's fnParseList from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033


select	*
from	yourtable t
	cross apply fnParseList(';', [appname]) p
where	p.Data like '%' + edit1txt + '%'

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-01-09 : 19:32:35

i second sunita on that. There are lots of resources / articles over there.

KH
[spoiler]Time is always against us[/spoiler]

mikedu
Starting Member

9 Posts

Posted - 2012-01-10 : 12:54:28

Thanks Sunita - that works like a charm!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources