Wildcard range search

Question

Wildcard range search

Viewing 10 posts - 16 through 24 (of 24 total)

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

Paul White SSC Guru Points: 150467 More actions · Answer 1

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.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150467 More actions · Answer 2

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);

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 3

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

Paul White SSC Guru Points: 150467 More actions · Answer 4

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 White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 5

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

Paul White SSC Guru Points: 150467 More actions · Answer 6

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 😎

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 7

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

Paul White SSC Guru Points: 150467 More actions · Answer 8

The Dixie Flatline (4/1/2010)
You've been going without sleep... haven't you?

Heh. 😀

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

SQLRNNR SSC Guru Points: 281334 More actions · Answer 9

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

Paul White SSC Guru Points: 150467 More actions · Answer 10

CirquedeSQLeil (4/1/2010)
Nice concept Paul

Not really anything new, as I said...but ok, thanks 🙂

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi