Wildcard range search
-
The Dixie Flatline (4/1/2010)
Paul, I think you overlooked this from AJ's post in midstream.The goal of the command is to find string within a wildcard range.
Ah right - must have been all those posts I had to scan over to find the point of the thread...;-)
I'll have another look then.
-
DECLARE @ExampleTABLE (Data VARCHAR(10)COLLATE LATIN1_GENERAL_BINUNIQUE NONCLUSTERED);SET NOCOUNT ON;INSERT @Example (Data) VALUES ('10');INSERT @Example (Data) VALUES ('20');INSERT @Example (Data) VALUES ('30');INSERT @Example (Data) VALUES ('40');INSERT @Example (Data) VALUES ('50');INSERT @Example (Data) VALUES ('60');INSERT @Example (Data) VALUES ('70');INSERT @Example (Data) VALUES ('80');INSERT @Example (Data) VALUES ('90');SELECT DataFROM @ExampleWHERE Data COLLATE LATIN1_GENERAL_BINBETWEEN '1' + CHAR(1)AND '5' + REPLICATE(CHAR(255), 9); -
Actually, I like this approach. The only thing I would suggest is to tweak it to accept character strings with a length greater than 1.
BETWEEN @lowParm + CHAR(1)
AND @highParm + REPLICATE(CHAR(255), len(number)-len(@highParm));
__________________________________________________
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 (4/1/2010)
Actually, I like this approach. The only thing I would suggest is to tweak it to accept character strings with a length greater than 1.Yep, it could certainly be made more general.
This is similar to the way the query optimizer (used to) turn a LIKE into a seek in some cases.
-
Paul, I'm curious. I understand right-padding the high value with char(255) to simulate a wildcard, but why add CHAR(1) to the low value?
__________________________________________________
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 (4/1/2010)
Paul, I'm curious. I understand right-padding the high value with char(255) to simulate a wildcard, but why add CHAR(1) to the low value?Just concept code, Bob. I didn't work too hard on it
-
You've been going without sleep... haven't 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 (4/1/2010)
You've been going without sleep... haven't you?Heh.
-
Paul White NZ (4/1/2010)
DECLARE @Example
TABLE (
Data VARCHAR(10)
COLLATE LATIN1_GENERAL_BIN
UNIQUE NONCLUSTERED
);
SET NOCOUNT ON;
INSERT @Example (Data) VALUES ('10');
INSERT @Example (Data) VALUES ('20');
INSERT @Example (Data) VALUES ('30');
INSERT @Example (Data) VALUES ('40');
INSERT @Example (Data) VALUES ('50');
INSERT @Example (Data) VALUES ('60');
INSERT @Example (Data) VALUES ('70');
INSERT @Example (Data) VALUES ('80');
INSERT @Example (Data) VALUES ('90');
SELECT Data
FROM @Example
WHERE Data COLLATE LATIN1_GENERAL_BIN
BETWEEN '1' + CHAR(1)
AND '5' + REPLICATE(CHAR(255), 9);
Nice concept Paul
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 -
CirquedeSQLeil (4/1/2010)
Nice concept PaulNot really anything new, as I said...but ok, thanks
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply