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
 General SQL Server Forums
 New to SQL Server Programming
 Query a string
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jp_fondu
Starting Member

10 Posts

Posted - 11/20/2013 :  06:36:59  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 11/20/2013 :  07:58:25  Show Profile  Reply with Quote
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
Go to Top of Page

jp_fondu
Starting Member

10 Posts

Posted - 11/20/2013 :  08:35:02  Show Profile  Reply with Quote
Thanks.
What is the query doing exacly? Just so I can undersand it.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3557 Posts

Posted - 11/20/2013 :  08:41:20  Show Profile  Reply with Quote
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.
Go to Top of Page

jp_fondu
Starting Member

10 Posts

Posted - 11/20/2013 :  10:25:59  Show Profile  Reply with Quote
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)) + ',%'
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3557 Posts

Posted - 11/20/2013 :  10:40:26  Show Profile  Reply with Quote
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)) + ',%'	
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 11/20/2013 :  12:56:48  Show Profile  Reply with Quote
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
Go to Top of Page

jp_fondu
Starting Member

10 Posts

Posted - 11/21/2013 :  04:20:50  Show Profile  Reply with Quote
pub.cir: -
cde,
seq-no,
applic-apno

pub.cirdetchc: -
cde,
dsc.

So the link would have to be cde?
Go to Top of Page

jp_fondu
Starting Member

10 Posts

Posted - 11/21/2013 :  04:52:14  Show Profile  Reply with Quote
and sorry no that was just me. the chc-list value is 2,4,12
Without the answer and brackets.
Go to Top of Page

jp_fondu
Starting Member

10 Posts

Posted - 11/21/2013 :  05:00:29  Show Profile  Reply with Quote
Syntax error in SQL statement at or about "?chc-list? + ',' LIKE '%,' + CAST(cirdet" (10713)State: S1000

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 11/21/2013 :  06:54:21  Show Profile  Reply with Quote
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
Go to Top of Page

jp_fondu
Starting Member

10 Posts

Posted - 11/21/2013 :  07:20:28  Show Profile  Reply with Quote
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 :/
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 11/21/2013 :  07:43:00  Show Profile  Reply with Quote
first try it in sql management studio and see what happens

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

jp_fondu
Starting Member

10 Posts

Posted - 11/21/2013 :  08:04:42  Show Profile  Reply with Quote
OK mate I'll give this a try and let you know.

Thanks.
Go to Top of Page

jp_fondu
Starting Member

10 Posts

Posted - 12/06/2013 :  04:51:26  Show Profile  Reply with Quote
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]
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 12/06/2013 :  05:10:07  Show Profile  Reply with Quote
cool
glad that you got it sorted

------------------------------------------------------------------------------------------------------
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.14 seconds. Powered By: Snitz Forums 2000