like query - 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)

like query

Author

Topic

Jomypgeorge
Starting Member

31 Posts

Posted - 2010-10-15 : 02:44:11

Hi,

i have to fetch a column like this

Select column from table where
column like ('a%','b%',c%').

like checking should be done for more than one value.

any hope?
thanks in advance

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-15 : 02:46:33

where column like 'a%'
or column like 'b%'
or column like 'c%'

No, you're never too old to Yak'n'Roll if you're too young to die.

Jomypgeorge
Starting Member

31 Posts

Posted - 2010-10-15 : 02:57:34

thanks webfred for your quick reply.
but my like parameter is a dynamic one which comes from front end.

not able to write a static query like this

any solutions?

Kristen
Test

22859 Posts

Posted - 2010-10-15 : 03:03:17

Use Dynamic SQL, or put the Front End values into a temporary table (e.g. using a SQL Splitter function) and do:


Select column 
from table
WHERE EXISTS
(
    SELECT *
    FROM   @TempTable
    WHERE  column LIKE TempTableValueColumn + '%'
)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-15 : 03:03:49

Please give more information.
Is it a call to a stored procedure?
How looks the given parameter?

No, you're never too old to Yak'n'Roll if you're too young to die.

Jomypgeorge
Starting Member

31 Posts

Posted - 2010-10-15 : 03:07:04

im using it in an SP with a parameter of nvarchar type
its value seems like 'a,b,c'

Jomypgeorge
Starting Member

31 Posts

Posted - 2010-10-15 : 03:09:31

thanks Kristen
but could u explain it?
sorry i couldn't understand it.

Kristen
Test

22859 Posts

Posted - 2010-10-15 : 03:12:17

Yeah I (or others) can describe it, but you'll have to ask some questions. I can't guess which part you don't understand!!

Kristen
Test

22859 Posts

Posted - 2010-10-15 : 03:12:39

P.S. I'm in meetings all day, but I'm sure someone else will be able to answer your questions

Jomypgeorge
Starting Member

31 Posts

Posted - 2010-10-15 : 03:18:00

thanks Kristen

i got what u said.
thanks a lot for your answer

Jomypgeorge
Starting Member

31 Posts

Posted - 2010-10-15 : 03:34:02

Kristen but my need is not done with your query

your query fetch all records from table if any column match with given parameter.
i need to fetch only the matching columns

any suggestions?

Jomypgeorge
Starting Member

31 Posts

Posted - 2010-10-15 : 03:40:31

sorry Kristen
it was my mistake with actual query.
you said what i really want.

Thanks a lot

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-15 : 04:24:12

[code]-- First we need a function to split the given parm array
-- You have to create it only once.
CREATE FUNCTION dbo.fnParseArray (@array VARCHAR(1000),@separator CHAR(1))
RETURNS @T Table (occ int,parmValue varchar(50))
AS
BEGIN
DECLARE @separator_position INT
DECLARE @array_value VARCHAR(1000)
declare @occurence int =1
if (left(@array,1)=@separator)
begin
set @array=stuff(@array,1,1,'')
end
if (right(@array,1)<>@separator)
begin
SET @array = @array + @separator
end
WHILE PATINDEX('%' + @separator + '%', @array) <> 0
BEGIN
SELECT @separator_position = PATINDEX('%' + @separator + '%',@array)
SELECT @array_value = LEFT(@array, @separator_position - 1)
INSERT into @T VALUES (@occurence,@array_value)
set @occurence = @occurence + 1
SELECT @array = STUFF(@array, 1, @separator_position, '')
END
RETURN
END

-- here we use a variable to test it
declare @search nvarchar(1000)
set @search='a%,b%,c%'

select * from yourTable
join dbo.fnParseArray(@search,',')
on yourColumn like parmValue

-- Hope you can adapt it to your needs![/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

Jomypgeorge
Starting Member

31 Posts

Posted - 2010-10-15 : 05:19:41

hi webfred

thanks a lot for your reply.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-15 : 05:33:41

welcome