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
 Using Like to check against a multivalue string
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

its_me
Starting Member

6 Posts

Posted - 09/28/2012 :  14:28:13  Show Profile  Reply with Quote
Hi All --

I am trying to write my query like this:

select fields from table t where someField = 'someThing'
and ', '|| t.REGION || ', ' like '%, ' || 'abc' ||', %'

But this only works if I am comparing a single value. If my string has multiple values, this qry returns no results.

my string can contain multiple values - For Ex: 'abc, def, xyz, hys' or it can be a single value like 'abc'.

Any ideas? I highly appreciate any help or ideas around this.

Thanks Much!!

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/28/2012 :  14:39:12  Show Profile  Reply with Quote
Are you using Microsoft SQL Server? This forum specializes in MS SQL Server, so experts on other DBMS are likely to be far and few in between. You might get better/faster answers at other forums such as dbforums.com
Go to Top of Page

its_me
Starting Member

6 Posts

Posted - 09/28/2012 :  14:41:57  Show Profile  Reply with Quote
I am using MS SQL Server
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/28/2012 :  14:51:18  Show Profile  Reply with Quote
I was asking only because that || operator is not valid in SQL Server. If your input string contains exact region names, you can use something like this:
SELECT fields FROM table1 t
WHERE somefield = 'something'
AND ','+'abc,efg,def'+',' LIKE '%,'+t.REGION+',%';
But, two problems with it: a) it is likely to be not very efficient. b) it won't work if you are looking for partial names.

To make it efficient and add the ability to look for partial strings, you will need a string splitter. A good one is here: http://www.sqlservercentral.com/articles/Tally+Table/72993/ You can copy and paste the code in Fig. 21 and run it to install the function.

Once you have the function, you can use it like this:
SELECT
	fields
FROM
	table1 t
	INNER JOIN  dbo.DelimitedSplit8K('abc,efg,def',',') d
		ON t.REGION LIKE '%'+d.Item+'%';
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