Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2014
    Posts
    35

    Unanswered: Attempting to select a range of values using wildcard & between or logical operators?

    Hi Everyone,

    I am in the midst of writing a query to return a range of product BIN LOCATIONS from a warehouse stock levels program.

    I know the start and end BIN LOCATIONS for the warehouse, e.g.: Start - #00000, and End - Z10000.

    However I cannot hard code these into the program, instead I want my operator to designate the first letter of both the start and end BINS locations, and then hit the 'go button'.

    Towards this end I have declared a couple of variables in my SQL, as follows -

    Code:
    declare @strBin varchar(10)
    set @strBin = ''
    if @strBin = ''
    	set @strBin = '#%'
    
    declare @endBin varchar(10)
    if @endBin = ''
    	set @endBin = 'z%'
    My challenge is writing a relevant WHERE clause for my select statement, I have tried the following -

    Code:
    where BINLOCAT.BINLABEL between @strBin and @endBin 
    order by BINLOCAT.BINLABEL
    Whilst the clause above does not cause any errors, neither does it return any results.

    My first thought is that the BETWEEN operator does not accept wildcards. But if this is correct then how do I go about allowing an operator to enter the start, and end BINS without typing the entire BIN string (e.g.: #00000)?

    I have tried >= @strBin and <= @endBin, but I am having a similar issue with no errors, and no data returned.

    Any help will be greatly appreciated.

    Kind Regards,

    David

  2. #2
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    We need DDL! What does a bin number look like? I might guess
    CREATE TABLE Warehouse
    ( ..
    bin_nbr CHAR(5) NOT NULL
    CHECK (bin_nbr LIKE '[0-9A-Z] [0-9][0-9][0-9][0-9]'),
    );

    Was I right?

    Forget the local variables; we do not use them declarative languages.

    bin_nbr BETWEEN (@start_bin_nbr, '00000') AND (@end_bin_nbr, 'Z9999')

  3. #3
    Join Date
    Mar 2014
    Posts
    35
    Hi Celko,

    Thank you for the response. Unfortunately I cannot provide any DDL because I am extracting data from a proprietary warehouse management package - and as a result I do not have access to the relevant information.

    I am using variables because I want to give the operator of my program the ability to stipulate start and end bin locations.

    Finally I did try the syntax that you kindly shared, however Server Management Studio 'didn't like the comma' between -

    (@start_bin_nbr, '00000')

    Kind Regards,

    David

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If all of your bin numbers all:
    1. Are six characters long
    2. The first character is any printing character
    3. The other characters being only zero through nine
    Then I'd construct a dynamic LIKE pattern on the app server or client application from the two characters provided by the user. In the case you provided (# being the first character and Z being the last character), I'd use:
    Code:
    DECLARE TABLE Warehouse_Bin_Locataion (
    -- Some DDL might go here
    bin_location    CHAR(6)   NOT NULL
       CONSTRAINT XCK_Warehouse_Bin_Locations_bin_location
          CHECK bin_location LIKE '[ -Z][0-9][0-9][0-9][0-9][0-9]')
    -- Other DDL might go here
    )
    I'd build the query like:
    Code:
    SELECT bin_location
       FROM Warehouse_bin_locations
       WHERE  bin_location LIKE '[#-Z][0-9][0-9][0-9][0-9][0-9]'
    I'd construct the LIKE pattern on the client.

    If you made other assumptions than I did about your table or column structure, this may need more work.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What about this:
    Code:
    DECLARE @strBin varchar(6)
        SET @strBin = '#'
    
    DECLARE @endBin varchar(6)
        SET @endBin = 'Z'
    
    SET @strBin = Left(@strBin + Replicate('0', 6), 6)
    SET @endBin = Left(@endBin + Replicate('9', 6), 6)
    
    SELECT *
    FROM   BINLOCAT
    WHERE  BINLABEL BETWEEN @strBin AND @endBin 
    ORDER
        BY BINLABEL
    This essentially expands your parameters to 6 characters: "#000000" and "Z99999" respectively.
    George
    Home | Blog

  6. #6
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    Parameters are not variables

    I am using variables because I want to give the operator of my program the ability to stipulate start and end bin locations.
    Variables are those local DECLARE things; they are bad. Parameters are passed with argument values in a procedure.

  7. #7
    Join Date
    Mar 2014
    Posts
    35
    Hi All,

    Thanks for your kind responses. I have tried the approach suggested by George, however it will not work exactly as I had hoped due to the fact that I was wrong in one fundamental way about the bin labels. Unfortunately they are not of a set length, in fact I have seen them at 3 characters up to a maximum of 8 characters since making my original post.

    Any other suggestions are welcome.

    Kind Regards,

    David

  8. #8
    Join Date
    Nov 2005
    Posts
    5
    Have you tried below SQL-

    SELECT bin_location
    FROM Warehouse_bin_locations
    WHERE bin_location LIKE '[#-Z]%'

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Minor modification for a variable length...
    Code:
    DECLARE @strBin varchar(6) --<-- the 6 represents the maximum length of the BINLOCAT.BINLABEL field
        SET @strBin = '#'
    
    DECLARE @endBin varchar(6)
        SET @endBin = 'Z'
    
    DECLARE @length tinyint
        SET @length = 4
    
    SET @strBin = Left(@strBin + Replicate('0', @length), @length)
    SET @endBin = Left(@endBin + Replicate('9', @length), @length)
    
    SELECT *
    FROM   BINLOCAT
    WHERE  BINLABEL BETWEEN @strBin AND @endBin 
    ORDER
        BY BINLABEL
    Unless I've misinterpreted the requirements..
    George
    Home | Blog

  10. #10
    Join Date
    Mar 2014
    Posts
    35
    Thanks everyone, ultimately I discovered that I had not set my variable correctly. As a result I fear that I have wasted some of the time that you have generously contributed to my problem. I ask that you forgive my original oversight, and I have learned a great deal from your contributions, which I am sure will be put to good use as I continue my MS SQL journey.

Posting Permissions

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