August 10, 2004 at 10:38 am
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?
August 10, 2004 at 2:01 pm
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
August 11, 2004 at 12:45 am
wow.. that's really good.
can you explain it a bit?
August 11, 2004 at 1:01 am
after studying some more i have a much better idea now. wow, that's really cool work. thanks.
August 11, 2004 at 9:54 am
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
August 31, 2004 at 6:09 am
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]
September 3, 2004 at 6:42 am
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?
September 3, 2004 at 6:42 am
double post
September 3, 2004 at 7:03 am
Neither Noel's solution nor the one I posted are dynamic.
What is it that you're really after?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
September 5, 2004 at 11:00 am
to also work for
SET @Filter = 'sydney,new york,boston,tokyo,beijing,athen'
arbitrary length
September 8, 2004 at 3:10 am
Can it be you have a problem with normalization?
Now you come up with such a string. One of the most referenced article regarding this you can read here http://www.sommarskog.se/arrays-in-sql.html
It might give you some ideas.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply