Automatically adding numbers to query

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)

Automatically adding numbers to query

Author

Topic

wndrboy2k3
Starting Member

37 Posts

Posted - 2008-06-04 : 12:53:01

Hello everyone!

I have a question for you. I have a product database with a lookup that pulls by product number. All my product numbers are made up the same way. IE. N59840, N00951, N00951. ect.

I have a stored procedure that looks up by that product number with a "LIKE" statement that looks like this.

WHERE ([Product#] LIKE '%' + @PRODUCTNUM + '%')

Which has this problem if someone types in "852" it returns

N00852
N05852
N98852

Is there anyway that I can have SQL put in zeros to fill up the 5 number spots so "852" brings up "00852" or "5852" brings up "05852"

I hope this makes sense.

Thank you for your help!!!

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-06-04 : 12:59:37

[code]WHERE [Product#] Like 'N' + right('0000' + @PRODUCTNUM, 5) + '%'[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-06-04 : 13:06:07

Yes you can do that.For example try this

declare @num int
set @num=852
select right('00000' + convert(varchar(5),@num),5)

Same way try for 5852

wndrboy2k3
Starting Member

37 Posts

Posted - 2008-06-04 : 13:22:39

omg you guys are amazing. thank you both worked very well.

wndrboy2k3
Starting Member

37 Posts

Posted - 2008-06-04 : 14:31:02

Hey I have a variation question for you now. For example the code

WHERE [Product#] Like 'N' + right('0000' + @PRODUCTNUM, 5) + '%'

the 'N' can also be a 'P' 'R' or 'Z' so i wrote it up as

WHERE [Product#] Like '_' + right('0000' + @PRODUCTNUM, 5) + '%'

but now when people type in

N0056 I am getting

N0056
P0056
Z0056

is there a way for me to tell SQL that if the N is supplied than to only select the N0056 but if it's not specified to return them all?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources