March 31, 2010 at 5:19 pm
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?
March 31, 2010 at 5:59 pm
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
March 31, 2010 at 6:12 pm
Wildcards only work with the LIKE operator, not >= or <=
March 31, 2010 at 6:25 pm
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.
March 31, 2010 at 8:14 pm
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.
March 31, 2010 at 9:27 pm
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
March 31, 2010 at 9:53 pm
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.
March 31, 2010 at 9:57 pm
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
March 31, 2010 at 10:01 pm
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
March 31, 2010 at 10:03 pm
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
March 31, 2010 at 10:08 pm
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
March 31, 2010 at 10:29 pm
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
March 31, 2010 at 10:35 pm
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
April 1, 2010 at 8:34 am
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 White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 1, 2010 at 8:41 am
???
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