Select Multiple Keywords - 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 2005 Forums

Transact-SQL (2005)

Select Multiple Keywords

Author

Topic

wgrc
Starting Member

1 Post

Posted - 2008-10-30 : 11:50:02

Hi everyone! A friend asked me a SQL question that I am not sure how to answer! He wants to split input into invidual words and then search a field for each word. For example if the user enters "OHIO PENNSYLVANIA VERMONT". he wants to then select from a particular column on each individual item, i.e. SELECT * FROM TABLE WHERE COL1 = "OHIO" OR COL1 = "VERMONT" OR COL1 = "PENNSYLVANIA". I wasn't sure of the best way to approach splitting the input in TSQL? (And I am assuming we don't ahve to worry about multi word values like NEW YORK) Thanks!

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2008-10-30 : 12:00:54

You can use LIKE operator instead of "=" and you need not to split words

SELECT * FROM TABLE WHERE COL1 LIKE '%OHIO%'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-30 : 12:17:01

some thing like this

SELECT *
FROM Table t
INNER JOIN (SELECT Val FROM dbo.ParseValues(@Param,' '))b
ON b.Val=t.Col1

pass @param's value as 'OHIO PENNSYLVANIA VERMONT'

function ParseValuies need to be created before as follows

CREATE FUNCTION ParseValues  
(@String varchar(8000),
@Delimiter char(1)  
)  
RETURNS @RESULTS TABLE  
(ID int identity(1,1),  
Val int  
)  
AS  
BEGIN  
DECLARE @Value varchar(100)  
  
WHILE @String is not null  
BEGIN  
SELECT @Value=CASE WHEN CHARINDEX(@Delimiter,@String) >0 THEN LEFT(@String,CHARINDEX(@Delimiter,@String)-1) ELSE @String END,  
@String=CASE WHEN CHARINDEX(@Delimiter,@String) >0 THEN SUBSTRING(@String,CHARINDEX(@Delimiter,@String)+1,LEN(@String)) ELSE NULL END  
INSERT INTO @RESULTS (Val)   
SELECT @Value   
END  
RETURN  
END

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-30 : 12:23:22

[code]declare @sample table (id int identity(1,1) not null primary key, State varchar(50), vote int)

insert into @sample
select 'OHIO',234 union all
select 'PENNSYLVANIA', 123 union all
select 'VERMONT', 312

declare @word varchar(8000)
set @word = 'OHIO PENNSYLVANIA VERMONT'
set @word = @word+space(1)
;with temp1 (word, state, starts) as
(select @word, cast('' as varchar(max)), 1
union all
select @word, cast(substring(@word, starts, charindex(space(1), @word, starts)-starts) as varchar(max)), charindex(space(1), @word, starts) +1
from temp1
where charindex(space(1), @word, starts) <> 0 --and starts < len(@word)
)
select * from @sample where state in (
select state from temp1 where state <> '')[/code]

cognos79
Posting Yak Master

241 Posts

Posted - 2008-10-30 : 13:07:31

you can also do like this...

say your input is separated by one blank space
Input: OHIO PENNSYLVANIA VERMONT

declare @input1 varchar(3)
,@input2 varchar(3)
,@input3 varchar(3)

set @param_format = '%s %s %s %s %s %s %s %s %s %s %s %s %s %s %s %s %s %s %s %s %s %s %s %s %s'

exec master..xp_sscanf
@parms,
@param_format,
@input1 OUTPUT,
@input2 OUTPUT,
@input3 OUTPUT

now you can use the @input1, @input2, @input3 values anywhere in you query.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-31 : 03:35:49

Serach for Array+sql server in google. You would find many methods

Madhivanan

Failing to plan is Planning to fail

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-10-31 : 04:52:54

[code]
declare @sample table (id int identity(1,1) not null primary key, State varchar(50), vote int)

insert into @sample
select 'OHIO',234 union all
select 'PENNSYLVANIA', 123 union all
select 'VEONT', 312

declare @word varchar(8000)
set @word = 'OHIO PENNSYLVANIA VERMONT'

SELECT *
FROM @sample
WHERE @word like '%' + State + '%'[/code]

[code]"There is only one difference between a dream and an aim. A dream requires soundless sleep to see, whereas an aim requires sleepless efforts to achieve..!!"
[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-31 : 05:01:29

what happens if i've something like GUINEA & PAPUA NEWGUINEA among the data. according to your logic wont it return both rows when i include GUINEA in search string. OP's initial post sounded as if OP's interested only in exact matches alone (i.e should get only 1 row with value GUINEA alone)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-31 : 05:04:00

quote:

Originally posted by PeterNeo


declare @sample table (id int identity(1,1) not null primary key, State varchar(50), vote int)

insert into @sample
select 'OHIO',234 union all
select 'PENNSYLVANIA', 123 union all
select 'VEONT', 312 

declare @word varchar(8000)
set @word = 'OHIO PENNSYLVANIA VERMONT'

SELECT *
FROM @sample
WHERE @word like '%' + State + '%'

"There is only one difference between a dream and an aim. A dream requires soundless sleep to see, whereas an aim requires sleepless efforts to achieve..!!"

Not relaible
See what happens if the value of @word is 'OHIOS PENNSYLVANIA VERMONT'

The proper way is

SELECT *
FROM @sample
WHERE ' '+@word+' ' like '% ' + State + ' %'

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-31 : 05:04:29

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources