Search in strings - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Search in strings

Author

Topic

the1gadget
Yak Posting Veteran

55 Posts

Posted - 2005-10-03 : 08:11:05

Hi All

I have a table with a comer separated list of values in it.

I want to return rows where part of the list matches one a set of values.

It could be done with a like statement but this is a big table.

Below is some code to create a table ind populate it.

====================8<====================
CREATE TABLE [dbo].[Project] (
[proj_id] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (50) NULL ,
[Mat_Id] [varchar] (50) NULL
) ON [PRIMARY]
GO

insert into Project(name,mat_id)values('House','1,3')
insert into Project(name,mat_id)values('Shop','3,5,6')
insert into Project(name,mat_id)values('Factory','5,6')
insert into Project(name,mat_id)values('School','2,7')
insert into Project(name,mat_id)values('Office','3,5')
insert into Project(name,mat_id)values('road','5')

select * from Project where mat_id like ('%2,%') or mat_id like ('%3,%')

====================8<====================

P.S. I do know it will match %2,% on a value like 22
P.P.S. I also know I could do it with a many to many linking table.

--
David

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-10-03 : 08:19:31

What you could do is create a function that returns a table variable from the input comma seperated variables and then join your query onto this table variable there are some examples of this on this site somewhere - I'll quickly check :)

Duane.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-10-03 : 08:20:48

i'd go with your P.P.S.

Go with the flow & have fun! Else fight the flow

the1gadget
Yak Posting Veteran

55 Posts

Posted - 2005-10-03 : 09:12:45

Hi Ditch

I have seen CSVtoInt and it looks the way to go.

--
David

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-10-03 : 09:20:43

great - I was sure I'd posted it somewhere just couldn't remember where and when.

Duane.

the1gadget
Yak Posting Veteran

55 Posts

Posted - 2005-10-03 : 09:31:53

Hi Ditch

Now I just have to work out how to use it.

:)

--
David

the1gadget
Yak Posting Veteran

55 Posts

Posted - 2005-10-03 : 11:43:12

Hi All.

My brain is turning to mush. I can not work out the SQL.

I want to something like the SQL below.

====================8<====================
select
a.*
from
project a, (select * from dbo.csvtoint(a.mat_id)) b
where
b.intval in dbo.csvtoint('2,3')
====================8<====================

--
David

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-10-03 : 11:46:17

have you heard of joins?
select
a.*
from project a join dbo.csvtoint(a.mat_id) b on a.mat_id = b.intval

Go with the flow & have fun! Else fight the flow

the1gadget
Yak Posting Veteran

55 Posts

Posted - 2005-10-03 : 11:55:43

Hi spirit1

Yes I have heard of joins, and I thought that I had tried that (I did say 'My brain is turning to mush.').

/me Goes looking

Oh I did, and got back an error.

select
a.*
from project a join dbo.csvtoint(a.mat_id) b on a.mat_id = b.intval

gives

Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near '.'.

--
David

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-10-03 : 12:09:17

Properly normalize your database and you will not be having these problems.

I know what you'll probably say, you don't have time, you like the way it looks this way, etc, etc, but if you learn how to properly store your data now you'll have a much faster, stabler, more accurate and easier to maintain database.

the1gadget
Yak Posting Veteran

55 Posts

Posted - 2005-10-04 : 05:13:31

Hi jsmith8858

I here what you are saying, because you are preaching to the converted.

The problem is that this is a database structure that I have inherited.

I am trying to get the go ahead to normilise the databasse but in the middle of large project with the goal posts moving it is difficult.

:)

--
David

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-10-04 : 06:00:59

usage would be something like this.........

INNER JOIN dbo.CSVTOINT(@DivisionID) td
ON d.DivisionID = td.intValue
INNER JOIN dbo.CSVTOINT(@CountryID) tc
ON (c.CountryID = tc.intValue OR @CountryID IS NULL)
INNER JOIN dbo.CSVTOINT(@InterestTypeID) tit
ON it.InterestTypeID = tit.intValue
INNER JOIN dbo.CSVTOINT(@RepaymentPeriodID) trp
ON rp.RepaymentPeriodID = trp.IntValue
INNER JOIN dbo.CSVTOINT(@MarketSectorID) tms
ON ms.MarketSectorID = tms.IntValue

Duane.

the1gadget
Yak Posting Veteran

55 Posts

Posted - 2005-10-05 : 10:35:31

Hi All

Just to say that I have managed to do what I need

===============================8<===============================
select distinct proj_id from
project a
, (select * from dbo.csvtoint('1,3')) b
where
a.Mat_id like ('%'+cast(b.intvalue as varchar(10))+'%')
===============================8<===============================

Not prety but it works.

--
David

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-10-05 : 10:54:52

oh oh...

this is slower for huge lists of conditions... but it works well for small lists:

Declare @list varchar(100)

Select distinct proj_id from project a Where ','+@list+',' like '%,'+A.Mat_Id+',%'

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-06 : 02:02:48

>>Select distinct proj_id from project a Where ','+@list+',' like '%,'+A.Mat_Id+',%'

Why do you need comma?
Wont this work?

Select distinct proj_id from project a Where @list like '%'+A.Mat_Id+'%'

Madhivanan

Failing to plan is Planning to fail

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-10-06 : 03:29:16

no it won't, the comma serves as a separator for each value

quote:
Originally posted by madhivanan

>>Select distinct proj_id from project a Where ','+@list+',' like '%,'+A.Mat_Id+',%'

Why do you need comma?
Wont this work?

Select distinct proj_id from project a Where @list like '%'+A.Mat_Id+'%'

Madhivanan

Failing to plan is Planning to fail

--------------------
keeping it simple...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-06 : 03:55:57

jen, what is wrong here?


Declare @table table (no int)
insert into @table values(32)
insert into @table values(23)
insert into @table values(456)
insert into @table values(55)

Declare @t varchar(10)
set @t='23,55'
select * from @table where @t like '%'+cast(no as varchar)+'%'

Madhivanan

Failing to plan is Planning to fail

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-10-06 : 03:58:25

what she means is it wont work for more than 2 entries.

Declare @table table (no int)
insert into @table values(32)
insert into @table values(23)
insert into @table values(456)
insert into @table values(55)

Declare @t varchar(10)
set @t='23,55,16,78,12,34'
select * from @table where @t like '%'+cast(no as varchar)+'%'

This doesn't work.

Duane.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-06 : 04:07:04

Am I missing anything?

Declare @table table (no int)
insert into @table values(32)
insert into @table values(23)
insert into @table values(456)
insert into @table values(55)

Declare @t varchar(10)
set @t='23,55,456'
select * from @table where @t like '%'+cast(no as varchar)+'%'
select * from @table where ','+@t+',' like '%,'+cast(no as varchar)+',%'

Both return
23
456
55

Madhivanan

Failing to plan is Planning to fail

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-10-06 : 04:10:51

no you are not - my mistake - i misunderstood the problem - sorry man

Duane.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-10-06 : 08:30:13

try it with this data set


Declare @table table (no int)
insert into @table values(32)
insert into @table values(3)
insert into @table values(23)
insert into @table values(456)
insert into @table values(55)

Declare @t varchar(10)
set @t='23,55,456'

select * from @table where @t like '%'+cast(no as varchar)+'%'

select * from @table where ','+@t+',' like '%,'+cast(no as varchar)+',%'

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources