Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2005
    Posts
    1

    Unanswered: Conditional Where wildcard problem

    Hi,

    I have a problem using the LIKE operator in a stored procedure. I have simplified the script so that it runs in query analyser and still have the same problem. The script is:

    DECLARE @FirstName varchar (50)

    SELECT @FirstName = 'B%'

    SELECT * FROM PhoneList
    WHERE PhoneList.FirstName LIKE CASE @FirstName WHEN '' THEN PhoneList.FirstName ELSE @FirstName END

    This code produces no rows in the result. However if I change the second line to:
    SELECT @FirstName = 'Ben'
    Then I get all of the rows with 'Ben' as the first name. If I change it to:
    SELECT @FirstName = 'Be%'
    Then I get all of the rows with three character first names beginning with 'Be'. If I change it to:
    SELECT @FirstName = 'B%%'
    Then I get all of the three character first names beginning with 'B'.

    I need the conditional where so that if an empty string is passed it returns every row, which works fine as it is.

    The % wildcard appears to be operating the same way as the _ wildcard. Has anyone seen this before?

    This is SQL Server 2k SP3 on Win2003 server.

    thanks
    Ben
    Last edited by Ben_Smith; 01-14-05 at 06:34. Reason: extra platform info

  2. #2
    Join Date
    Jan 2005
    Posts
    8
    Hi,

    maybe you could try this:

    DECLARE @FirstName varchar (50)

    SELECT @FirstName = 'B%'

    SELECT * FROM PhoneList
    WHERE PhoneList.FirstName LIKE @FirstName + '%'

    If @FIrstName is an empty string the statement should return all data.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •