how: SQL matching two multiple valued strings

Question

Post reply

how: SQL matching two multiple valued strings

cyberburns

SSC Enthusiast

Points: 113
More actions
August 10, 2004 at 10:38 am

#161868

I am a little stomped and wandering if someone might have an idea how to go about doing this.

following on from this guide http://www.4guysfromrolla.com/webtech/031004-1.shtml on matching a comma-delimited string, I would like to expand on this and match two comma-delimited string in a sproc.

In my database, table A have a city field containing a comma delimited string ie 'sydney, new york, chicago'. I am passing a similar comma-delimited string to sproc and returning matches.

so, we have table A:

id/city

1/sydney, new york, chicago

2/new york, san antonio

3/beijing, sydney

4/london,beijing

passing string 'sydney, new york'

need to return: id 1,2,3 (1,2 match new york and 1,3 matching sydney)

any ideas?

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

noeld SSC Guru Points: 96590 More actions · Answer 1

I am going to code what you asked for just for the fun of it but I STRONGLY recommend you to redesing your tables to a 3NF

Note that there can not be spaces between commas and that the filter is prepended and appended with commas ','

create table Cty (id int, city varchar(2000))

insert into Cty (id, City) values(1, 'sydney,new york,chicago')

insert into Cty (id, City) values(2, 'new york,san antonio')

insert into Cty (id, City) values(3, 'beijing,sydney')

insert into Cty (id, City) values(4, 'london,beijing')

Declare @Filter varchar (20)

set @filter = ',sydney,new york,'

select distinct id

from Cty

cross join

(

select substring(@filter,number + 1, charindex(',',@Filter,number+1) - number -1) CtyLookup

from

(select number

from master..spt_values

where number < len(@filter) and type = 'P' and number > 0) Q

where substring(@filter,number,1) = ','

)T

where Len(replace( city, CtyLookup,'')) <> Len(City)

id

1

2

3

* Noel

cyberburns SSC Enthusiast Points: 113 More actions · Answer 2

cyberburns

SSC Enthusiast

Points: 113

August 11, 2004 at 12:45 am

#518412

wow.. that's really good.

can you explain it a bit?

cyberburns SSC Enthusiast Points: 113 More actions · Answer 3

cyberburns

SSC Enthusiast

Points: 113

August 11, 2004 at 1:01 am

#518414

after studying some more i have a much better idea now. wow, that's really cool work. thanks.

jbwsql SSC Rookie Points: 43 More actions · Answer 4

jbwsql

SSC Rookie

Points: 43

August 11, 2004 at 9:54 am

#518517

After nine months of subscribing to this site. This is my first reply to a post.

That was awsome!! I ran each part of the script independently and I still trying to grasp the entire concept.

Can you explain spt_values, I attempted to use sp_helptext and the general bol and didn't find it.

Thanks

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

While I'm with Noel on the obvious state of your data, I disagree with his solution. A cross join can be a performance killer when it come to larger datasets; spt_values is undocumented helper table that is used by some internal procedures by SQL Server itself and finally a number table isn't needed anyway. I believe this to be a better solution:

SET NOCOUNT ON

CREATE TABLE Cty (id INT, city VARCHAR(2000))

INSERT INTO Cty (id, City) VALUES(1, 'sydney,new york,chicago')

INSERT INTO Cty (id, City) VALUES(2, 'new york,san antonio')

INSERT INTO Cty (id, City) VALUES(3, 'beijing,sydney')

INSERT INTO Cty (id, City) VALUES(4, 'london,beijing')

DECLARE @Filter varchar(20)

SET @Filter = 'sydney,new york'

SELECT

Id

FROM

Cty

WHERE

City LIKE '%'+ PARSENAME(REPLACE(@Filter,',','.'),1) + '%'

OR

City LIKE '%'+ PARSENAME(REPLACE(@Filter,',','.'),2) + '%'

DROP TABLE Cty

SET NOCOUNT OFF

Id

-----------

1

2

3

HTH

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

cyberburns SSC Enthusiast Points: 113 More actions · Answer 6

even i have to agree it'd be best if the data was normalised! As I will only be involved in a change request, I prefer not to interfere with the data and leave the db structure as is.

Frank, your solution is definitely cleaner and neater! But it's not dynamic, is the only way to implement this to parse the string, create a dynamic query and execute?