Wildcards and numbers - 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

General SQL Server Forums

New to SQL Server Programming

Wildcards and numbers

Author

Topic

Pinto
Aged Yak Warrior

590 Posts

Posted - 2007-04-18 : 07:11:35

How do Iput wildcards around a number in an sp ? If my user leaves BoxNo blank it will list all boxes

SELECT *
FROM tblFiles
WHERE
ConNo =@strRMUConsignmentNo
and FileRef like '%'+@strtxtFileRef+'%'
and Subject like '%'+@strtxtSubject+'%'
and FileDescription like '%'+@strtxtDescription+'%'
and BoxNo like %+@strBoxNo%

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-18 : 07:14:37

BoxNo is integer field? or varchar?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-04-18 : 07:15:06

SELECT *
FROM tblFiles
WHERE
ConNo =@strRMUConsignmentNo
and FileRef like '%'+@strtxtFileRef+'%'
and Subject like '%'+@strtxtSubject+'%'
and FileDescription like '%'+@strtxtDescription+'%'
and (BoxNo = @strBoxNo or boxno is null)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-18 : 07:15:07

and cast(BoxNo as varchar) like '%' + @strBoxNo + '%'

Peter Larsson
Helsingborg, Sweden

Pinto
Aged Yak Warrior

590 Posts

Posted - 2007-04-18 : 08:40:33

BoxNo is Integer. I will try the other solutions. Thank you

Pinto
Aged Yak Warrior

590 Posts

Posted - 2007-04-18 : 08:54:34

I have used PbGuy suggestion as if they enter a 2 they will get boxes 2, 22, 222, 23 etc etc I wanted 2 only.

Pinto
Aged Yak Warrior

590 Posts

Posted - 2007-04-18 : 09:11:08

Sorry - it does not return any records if they leave BoxNo blank. It works if a BoxNo is entered. Every record will have a BoxNo, but the user may filter on this if they want to, or list all boxes by leaving BoxNo blank.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-18 : 09:21:57

[code]and (BoxNo = @BoxNo or @BoxNo = '')[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Pinto
Aged Yak Warrior

590 Posts

Posted - 2007-04-18 : 10:39:44

That does not work because every record will have a BoxNo so if one is not entered to filter on no records satisfy Boxno = ''

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-18 : 10:42:15

That is NOT the column criteria. It's the parameter criteria.
Have you tried the any of the suggestions made to you?

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-18 : 10:44:37

Have you tried my solution at all?

It says:

and (BoxNo = @BoxNo or @BoxNo = '')

and not:

and (BoxNo = @BoxNo or BoxNo = '')

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Pinto
Aged Yak Warrior

590 Posts

Posted - 2007-04-18 : 11:10:50

Sorry :( I have tried the solutions and a mix of the solutions.

Here it is time to go home - I will try again tomorrow.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-04-18 : 14:32:40

I think BPGuy had the solution. Here is some code to try out:

DECLARE @Table TABLE (BoxNo INT)
DECLARE @BoxNo VARCHAR(10)

SET @BoxNo = '2'
--SET @BoxNo = ''

INSERT @Table
SELECT 2 UNION ALL
SELECT 22 UNION ALL
SELECT 23 UNION ALL
SELECT 40 UNION ALL
SELECT 32 


SELECT *
FROM @Table
WHERE BoxNo = CAST(@BoxNo AS INT) OR @BoxNo = '' -- OR @BoxNo IS NULL

Depending on what @BoxNo is when the user does not specify anything blank or NULL, you may need to adjust the WHERE clause.

-Ryan

Pinto
Aged Yak Warrior

590 Posts

Posted - 2007-04-19 : 04:26:33

Here is my sp now which nearly works !

If I enter a Consignment number only - it works
if I enter a Consignment number and Box number and suffix - it works
If I enter a Consignment number and a Box number and no suffix - it returns boxes with that box number, with a suffix and without a suffix.

If there is Box 1A and Box 1 I only want the 1A boxes displayed if a suffix is entered.

CREATE PROCEDURE spRMU_GettblFilesandBox

@strRMUConsignmentNo nvarchar(10),
@strViewRestricted bit,
@strtxtFileRef nvarchar (100),
@strtxtSubject nvarchar (100),
@strtxtDescription nvarchar (100),
@strBoxNo Integer,
@strBoxSuffix nvarchar (5)

AS

IF @strViewRestricted = 1
BEGIN
SELECT *
FROM tblFiles
WHERE
ConNo =@strRMUConsignmentNo
and FileRef like '%'+@strtxtFileRef+'%'
and Subject like '%'+@strtxtSubject+'%'
and FileDescription like '%'+@strtxtDescription+'%'
and (BoxNo = @strBoxNo or @strBoxNo = '')
and (BoxSuffix=@strBoxSuffix or @strBoxSuffix ='')

ORDER BY Id
END

IF @strViewRestricted = 0

BEGIN
SELECT *
FROM tblFiles
WHERE
ConNo =@strRMUConsignmentNo and
Restricted = 0
and FileRef like '%'+@strtxtFileRef+'%'
and Subject like '%'+@strtxtSubject+'%'
and FileDescription like '%'+@strtxtDescription+'%'
and (BoxNo = @strBoxNo or @strBoxNo = '')
and (BoxSuffix=@strBoxSuffix or @strBoxSuffix ='')

ORDER BY Id
END
GO

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-19 : 04:30:25

SELECT *
FROM tblFiles
WHERE
ConNo =@strRMUConsignmentNo and
Restricted <= @strViewRestricted -- no need for dual select
and FileRef like '%'+@strtxtFileRef+'%'
and Subject like '%'+@strtxtSubject+'%'
and FileDescription like '%'+@strtxtDescription+'%'
and (BoxNo = @strBoxNo or @strBoxNo = '')
and (BoxSuffix=@strBoxSuffix or @strBoxSuffix ='')

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources