MS SQL Server - Need help in Multiple String Search

Author

Need help in Multiple String Search

Luq K

2005-04-29, 11:23 am

Hi,
I want to search for multiple strings in a comma seperated text field e.g

text1(John,Abbot,Bob
by,Mark)

I want to get the record which has Booby or Mark.

I can use
Where text1 like '%Bobby%',
but this statement will only search for Bobby in the text1 field, I want it
to search for Bobby or Mark or any other text that I want to search for in a
comma seperated text field.

Thanks in Advance

Sincerely
--
Luq K

Alejandro Mesa

2005-04-29, 11:23 am

You can create a function to split the list and return a table, then you can
use that table to join with the one you are looking in.

select a.*
from table1 as a inner join dbo. split_func('John,Abb
ot,Bobby,Mark') as t(colA)
on a.c1 like '%' + t.colA + '%'

Arrays and Lists in SQL Server
http://www.sommarskog.se/arrays-in-sql.html

Faking arrays in T-SQL stored procedures
http://www.bizdatasolutions.com/tsql/sqlarrays.asp

How do I simulate an array inside a stored procedure?
http://www.aspfaq.com/show.asp?id=2248

AMB

"Luq K" wrote:

> Hi,
> I want to search for multiple strings in a comma seperated text field e.g
>
> text1(John,Abbot,Bob
by,Mark)
>
> I want to get the record which has Booby or Mark.
>
> I can use
> Where text1 like '%Bobby%',
> but this statement will only search for Bobby in the text1 field, I want it
> to search for Bobby or Mark or any other text that I want to search for in a
> comma seperated text field.
>
>
> Thanks in Advance
>
> Sincerely
> --
> Luq K

Luq K

2005-04-29, 11:23 am

Thanks Alejandro,

I have a situation like this,
table1.Field1
Rec1 = John,Abbot,Bobby,Mar
k
Rec2 = Abbot,Mark
Rec3 = John,Mark

What I want is

Select *
from table1
where field1 like '%Abbot%'
OR field1 like '%Bobby%'

I will get Rec1 and Rec2 in return, since my search string is extensive and
don't want to use too many OR clauses is there a clean way of doing this?

Thanks

Sincerely,
--
Luq K

"Alejandro Mesa" wrote:
[color=darkred]
> You can create a function to split the list and return a table, then you can
> use that table to join with the one you are looking in.
>
> select a.*
> from table1 as a inner join dbo. split_func('John,Abb
ot,Bobby,Mark') as t(colA)
> on a.c1 like '%' + t.colA + '%'
>
> Arrays and Lists in SQL Server
> http://www.sommarskog.se/arrays-in-sql.html
>
> Faking arrays in T-SQL stored procedures
> http://www.bizdatasolutions.com/tsql/sqlarrays.asp
>
> How do I simulate an array inside a stored procedure?
> http://www.aspfaq.com/show.asp?id=2248
>
>
> AMB
>
> "Luq K" wrote:
>

Alejandro Mesa

2005-04-29, 1:23 pm

Example:

use northwind
go

-- this function was taken from one of the links posted
-- How do I simulate an array inside a stored procedure?
CREATE FUNCTION dbo. FAQ_CommaSeparatedLi
stToSingleColumn
(
@cslist VARCHAR(8000)
)
RETURNS @t TABLE
(
Item VARCHAR(64)
)
BEGIN
DECLARE @spot SMALLINT, @str VARCHAR(8000), @sql VARCHAR(8000)

WHILE @cslist <> ''
BEGIN
SET @spot = CHARINDEX(',', @cslist)
IF @spot>0
BEGIN
SET @str = LEFT(@cslist, @spot-1)
SET @cslist = RIGHT(@cslist, LEN(@cslist)-@spot)
END
ELSE
BEGIN
SET @str = @cslist
SET @cslist = ''
END
INSERT @t SELECT @str
END
RETURN
END
GO

create table t1 (
c1 varchar(255)
)
go

insert into t1 values('John,Abbot,B
obby,Mark')
insert into t1 values('Abbot,Mark')

insert into t1 values('John,Mark')
go

select
*
from
dbo. FAQ_CommaSeparatedLi
stToSingleColumn('Jo
nh,Mark,Bobby')
go

select distinct
t1.c1
from
t1
inner join
dbo. FAQ_CommaSeparatedLi
stToSingleColumn('Jo
nh,Bobby') as t2
on t1.c1 like '%' + t2.Item + '%'
go

drop table t1
go

drop FUNCTION dbo. FAQ_CommaSeparatedLi
stToSingleColumn
go

AMB

"Luq K" wrote:
[color=darkred]
> Thanks Alejandro,
>
>
> I have a situation like this,
> table1.Field1
> Rec1 = John,Abbot,Bobby,Mar
k
> Rec2 = Abbot,Mark
> Rec3 = John,Mark
>
> What I want is
>
> Select *
> from table1
> where field1 like '%Abbot%'
> OR field1 like '%Bobby%'
>
> I will get Rec1 and Rec2 in return, since my search string is extensive and
> don't want to use too many OR clauses is there a clean way of doing this?
>
>
> Thanks
>
>
> Sincerely,
> --
> Luq K
>
>
> "Alejandro Mesa" wrote:
>

Sponsored Links