Help using Like and % in stored Proc

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)

Help using Like and % in stored Proc

Author

Topic

Johnhamman
Starting Member

37 Posts

Posted - 2002-04-04 : 21:33:16

Hi all I have a stored proc that has this for code.


CREATE PROCEDURE [spCheckIP]
(
@IPaddy as varchar(15)
)
 AS
Select sIPaddr
From Spiderlist
where sIPaddr like @IPaddy

I need to do a search for ip addr (xxx.xxx.xxx format) that match addrs that are in the xxx.xxx.xxx.xxx format.

when trying to test this it would not pull any results.
this was my test code.
Select sIPaddr
From Spiderlist
where sIPaddr like '123.123.123'

now when i did this it produced the results i wanted
Select sIPaddr
From Spiderlist
where sIPaddr like '123.123.123%'

how do i automaticly put into my stored proc the % into the end of @IPaddy so that it will bring proper results back? is there a way of doing this or will i have to do this in the code layer? and which is more effeciant.
john

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-04 : 21:36:49

CREATE PROCEDURE [spCheckIP] (@IPaddy as varchar(15) )
AS
Select sIPaddr
From Spiderlist
where sIPaddr like Ltrim(Rtrim(@IPaddy)) + '%'

I put the trim functions in there to remove any spaces that might sneak in; you can take them out if there's not chance of rogue spaces.

Johnhamman
Starting Member

37 Posts

Posted - 2002-04-04 : 21:41:36

outstanding Thanks!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources