Using wildcards with LIKE on a parameter in stored procedure

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)

Using wildcards with LIKE on a parameter in stored procedure

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-05-30 : 08:47:34

Gregory writes "Using MSDE I have tried to create a stored procedure that accepts a text search parameter from my application, but I want to do a wildcard search on the parameter.

I have tried this but it doesnt seem to do a wildcard search.

CREATE PROCEDURE search_last @searchStr char(20) AS
SELECT lastName, firstName, cust_id
FROM cust_table
WHERE lastName
LIKE (@searchStr + '%')
GO

Am I doing something wrong? Or is there a way to tack on the % to the contents of the parameter within the stored procedure? Is there a better way to do this not using the LIKE keyword?

Many thanks for any advice. (BTW: first day playing with SQL so i'm a bit of a newb...)"

raclede
Posting Yak Master

180 Posts

Posted - 2005-05-30 : 21:40:47

use varchar instead of char it will work..

"If the automobile had followed the same development cycle as the computer, a Rolls-Royce would today cost $100, get a million miles per gallon, and explode once a year, killing everyone inside. "

raclede

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-05-30 : 22:47:25

reason

quote:

Fixed-length (char) or variable-length (varchar) character data types

declare @varchar varchar(10),@char char(10)

select @varchar='varchar',@char='varchar'

select datalength(@varchar) as 'varchar',datalength(@char) as 'char'

select len(@varchar) as 'varchar',len(@char) as 'char'

--visually look at the length of the column, you see extra spaces at the end of the string with 'untrimmed' @char
select @varchar + ' like ' + @char
select @varchar + ' like ' + rtrim(@char)

if @varchar like (rtrim(@char) + '%')
select 'rtrim works or just use varchar if you don't need fixed length parameter'

HTH

--edit for alternative aside from like
explore patindex or charindex

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

meenu_sow
Starting Member

5 Posts

Posted - 2005-05-31 : 05:50:22

create PROCEDURE search_last @searchStr varchar(20) AS
SELECT [name] FROM sysobjects WHERE [name] LIKE @searchStr + '%'
GO

Meenakshi

vivek.kumargupta
Starting Member

45 Posts

Posted - 2005-05-31 : 10:54:47

create proc wild (
@var nvarchar(10)
)
as
select * from roysched
where title_id like @var + '%'

Use this query (designed on pubs DB)

Thanks,
Vivek

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources