Query a string - 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

Query a string

Author

Topic

jp_fondu
Starting Member

10 Posts

Posted - 2013-11-20 : 06:36:59

I hope I can explain this in a way that makes sense.

I have a query on a peson application that poduces a record.
Within the application there is a question that allows the applicant to choose several answers: -

Applic_no Question_ID Question Answer
12345 40 Medical 2,5,12

There is a lookup table that tells me what each answer is: -
ID Desc
1 Stairlift
2 Wheelchair
3 Walk-in shower
5 Ramp
12 WC Downstairs

However, how do I query the lookup table if my answer is 2,5,12? I need to somehow split it or query the string array to pick out the values seperated by commas.

Any help would be apprecited.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-20 : 07:58:25

you need to use something like below


SELECT m.Applic_no,m.Question_ID,m.Question,l.Desc AS Answer
FROM MainTable m
INNER JOIN LookupTable l
ON ',' + m.Answer + ',' LIKE '%,' + CAST(ID AS varchar(5)) + ',%'

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

jp_fondu
Starting Member

10 Posts

Posted - 2013-11-20 : 08:35:02

Thanks.
What is the query doing exacly? Just so I can undersand it.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-11-20 : 08:41:20

The LIKE clause returns true if the pattern on the right side exists in the string on the left side. So in the query ',' + m.Answer + ',' LIKE '%,' + CAST(ID AS varchar(5)) + ',%', in your example, the left side would be ,2,5,12,. On the right side, for row 1 of the lookup table, it would be '%,1,%' - which does not match the string on the left side. For the second row, the right side of the like clause would be '%,2,%' which does match the left side. And so on.

jp_fondu
Starting Member

10 Posts

Posted - 2013-11-20 : 10:25:59

Thanks James I really appreciated it. I have it working up until the last part. My descriptions above where quick desciptions but here is the real query. Many Thanks

NOTE:
cir.seq-no = question ID(Medical)
cir."chc-list" = Answer (2,4,12)
pub.cirdetchc = lookup table
cirdetchc.num = ID
cirdetchc.dsc = desc

SELECT
cir."applic-apno",
cir."seq-no",
cir."cde",
cirdetchc.dsc

FROM
pub.cir,
pub.cirdetchc

INNER JOIN pub.cirdetchc
ON ',' + cir."chc-list" + ',' LIKE '%,' + CAST(cirdetchc.num AS varchar(5)) + ',%'

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-11-20 : 10:40:26

Do you have those parentheses and the word "Answer" around the on chc-list? If so you need to remove those. For example like this:

....
INNER JOIN pub.cirdetchc
ON ',' + REPLACE(REPLACE(cir."chc-list",')',''),'Answer (','') + ',' 
	LIKE '%,' + CAST(cirdetchc.num AS varchar(5)) + ',%'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-20 : 12:56:48

how are the tables pub.cir,pub.cirdetchc related?
the way you've written it, it will cause a cross join between them.

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

jp_fondu
Starting Member

10 Posts

Posted - 2013-11-21 : 04:20:50

pub.cir: -
cde,
seq-no,
applic-apno

pub.cirdetchc: -
cde,
dsc.

So the link would have to be cde?

jp_fondu
Starting Member

10 Posts

Posted - 2013-11-21 : 04:52:14

and sorry no that was just me. the chc-list value is 2,4,12
Without the answer and brackets.

jp_fondu
Starting Member

10 Posts

Posted - 2013-11-21 : 05:00:29

Syntax error in SQL statement at or about "?chc-list? + ',' LIKE '%,' + CAST(cirdet" (10713)State: S1000

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-21 : 06:54:21

quote:
Originally posted by jp_fondu

Syntax error in SQL statement at or about "?chc-list? + ',' LIKE '%,' + CAST(cirdet" (10713)State: S1000

where did '?' etc come from? are you using sql server?

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

jp_fondu
Starting Member

10 Posts

Posted - 2013-11-21 : 07:20:28

Im using Business Objects butting running a free hand SQL query through it.
I'm not sure where its getting the ? from. I ran this: -

SELECT
cir."applic-apno",
cir."seq-no",
cir."cde",
cirdetchc.dsc

FROM
pub.cir,
pub.cirdetchc

INNER JOIN pub.cirdetchc
ON ',' + cir.”chc-list” + ',' LIKE '%,' + CAST(cirdetchc.num AS varchar(5)) + ',%'

Sorry this is dragging out :/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-21 : 07:43:00

first try it in sql management studio and see what happens

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

jp_fondu
Starting Member

10 Posts

Posted - 2013-11-21 : 08:04:42

OK mate I'll give this a try and let you know.

Thanks.

jp_fondu
Starting Member

10 Posts

Posted - 2013-12-06 : 04:51:26

Thanks mate finally got it working. I really appreciate the help. This was the end query: -

SELECT
cir.[applic-apno],
cir.[seq-no],
cir.[cde],
cirdetchc.dsc,
cirdetchc.num,
CAST(cirdetchc.num AS varchar(5))

FROM
cir

INNER JOIN cirdetchc
ON (',' + cir. [chc-list] + ',' LIKE '%,' + CAST(cirdetchc.num AS varchar(5)) + ',%')
AND cir.[seq-no] = cirdetchc.[seq-no]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-06 : 05:10:07

cool
glad that you got it sorted

------------------------------------------------------------------------------------------------------
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