Wildcard range search

  • I would like to use a wildcard in a where clause to find records based on the value being between two values.

    E.g. FieldName >= '1%' and FieldName <= '5%'

    When I execute this type of query, the result is that the right side of the between becomes 'less than', not 'less than or equal to'.

    E.g. try this example below. Any explanations would be most appreciated.

    IF OBJECT_ID('Example') IS NOT NULL

    DROP TABLE [Example]

    CREATE TABLE dbo.Example (Number varchar(50) NULL) ON [PRIMARY]

    SET NOCOUNT ON

    INSERT INTO Example (Number) VALUES ( '10' )

    INSERT INTO Example (Number) VALUES ( '20')

    INSERT INTO Example (Number) VALUES ( '30')

    INSERT INTO Example (Number) VALUES ( '40')

    INSERT INTO Example (Number) VALUES ( '50')

    INSERT INTO Example (Number) VALUES ( '60')

    INSERT INTO Example (Number) VALUES ( '70')

    INSERT INTO Example (Number) VALUES ( '80' )

    INSERT INTO Example (Number) VALUES ( '90' )

    SELECT Number From Example WHERE Number >= '1%' AND Number <= '5%'

    The result is 10,20,30 and 40 is returned. What gives?

  • The same result occurs if you eliminate the >= '1%'.

    If you do it this way though, it will work.

    Declare @var1varchar(10)

    ,@var2varchar(10)

    Select @var1 = '1%',@var2 = Number

    From Example

    where Number like '5%'

    Print @var1

    Print @var2

    SELECT Number From Example

    WHERE Number >= @var1

    AND Number <= @var2

    Seems a little unnecessary to need to do this much extra though.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Wildcards only work with the LIKE operator, not >= or <=

  • What I think you're seeing here is something very similar to when checking dates.

    When doing a date range, something like '1-Apr-2010" is interpreted as '1-Apr-2010 12:00:00am'. This means that "<= '1-Apr-2010'" will not include '1-Apr-2010 1:00:00am'.

    The wildcard in the string search isn't being ignored, but it's taking the minimum "value" it can be as the upper limit (probably empty string). Therefore the upper boundary is '5', rather than the '5 plus any character' you're intending it to be.

  • Glenn Dorling (3/31/2010)


    What I think you're seeing here is something very similar to when checking dates.

    When doing a date range, something like '1-Apr-2010" is interpreted as '1-Apr-2010 12:00:00am'. This means that "<= '1-Apr-2010'" will not include '1-Apr-2010 1:00:00am'.

    The wildcard in the string search isn't being ignored, but it's taking the minimum "value" it can be as the upper limit (probably empty string). Therefore the upper boundary is '5', rather than the '5 plus any character' you're intending it to be.

    Thanks for the reply. I have found it interesting that there is very little (if any) discussion on wildcard use outside of the "LIKE" clause.

    Your conclusion seems to make sense, and I'll work some logic into my app that deals with the <= side of things to ensure that it takes the 'ceiling' of the string, so to speak.

  • I agree with Michael:

    Wildcards only work with the LIKE operator, not >= or <=

    In your example the numbers are varchar datatyped, not numeric. To find 10,20,30,40,50, try this:

    WHERE number >= '1' and number < '6' -- less than, not less than or equal to

    Be aware that in varchar strings '11' is greater than '1' but less than '2'. So the example above would return strings such as '5000' or '5A' as well.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • The Dixie Flatline (3/31/2010)


    I agree with Michael:

    Wildcards only work with the LIKE operator, not >= or <=

    In your example the numbers are varchar datatyped, not numeric. To find 10,20,30,40,50, try this:

    WHERE number >= '1' and number < '6' -- less than, not less than or equal to

    Be aware that in varchar strings '11' is greater than '1' but less than '2'. So the example above would return strings such as '5000' or '5A' as well.

    Hi, yes I'm aware that this is not a numeric comparison. I guess my simplistic example was off the mark.

    The goal of the command is to find string within a wildcard range. Glenn's comment makes sense, and I'm going to work with that idea.

  • FROM BOL:

    Wildcards used without LIKE are interpreted as constants instead of as a pattern, that is they represent only their own values.

    http://msdn.microsoft.com/en-us/library/ms187489%28v=SQL.100%29.aspx

    With respect to Glenn, this is nothing like a datetime comparison.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • The Dixie Flatline (3/31/2010)


    FROM BOL:

    Wildcards used without LIKE are interpreted as constants instead of as a pattern, that is they represent only their own values.

    http://msdn.microsoft.com/en-us/library/ms187489%28v=SQL.100%29.aspx

    Thanks for providing that link.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Anything for you, Jason, you copyright-violator, you.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • The Dixie Flatline (3/31/2010)


    Anything for you, Jason, you copyright-violator, you.

    I'm thinking it is probably closer to trademark infringement. And wouldn't it belong to the community anyway. 😉

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • I didn't say *I* was going to sue you. I was encouraging STEVE to. 😉

    Excuse us, AJ... private joke.

    I still think greater than or equal to '1' and less than '6' is going to be the answer.

    You could say:

    WHERE LEFT(number,1) BETWEEN '1' and '5'

    But that is going to force a scan.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • Yeah, apologies for hijacking the thread with an inside joke.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • DECLARE @Example

    TABLE (

    Number INTEGER NULL

    );

    SET NOCOUNT ON;

    INSERT @Example (Number) VALUES ( 10 );

    INSERT @Example (Number) VALUES ( 20 );

    INSERT @Example (Number) VALUES ( 30 );

    INSERT @Example (Number) VALUES ( 40 );

    INSERT @Example (Number) VALUES ( 50 );

    INSERT @Example (Number) VALUES ( 60 );

    INSERT @Example (Number) VALUES ( 70 );

    INSERT @Example (Number) VALUES ( 80 );

    INSERT @Example (Number) VALUES ( 90 );

    SELECT Number

    FROM @Example

    WHERE Number >= 10

    AND Number < 60;

  • ???

    Paul, I think you overlooked this from AJ's post in midstream.

    Hi, yes I'm aware that this is not a numeric comparison. I guess my simplistic example was off the mark.

    The goal of the command is to find string within a wildcard range.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Viewing 15 posts - 1 through 15 (of 24 total)

You must be logged in to reply to this topic. Login to reply