MadIce
asked on
Query range with wildcard criteria
How can I query a range that includes wildcard. example data
GG-93A123
GG-94A222
GG-94B233
GG-03A434
GG-05B555
GG-05C343
GG-05F433
GG-06A665
So what I want is anything between GG-94B% and GG-05C%
GG-94B233
GG-03A434
GG-05B555
GG-05C343
Thanks in advance. Using Microsoft SQL Server Management Studio
GG-93A123
GG-94A222
GG-94B233
GG-03A434
GG-05B555
GG-05C343
GG-05F433
GG-06A665
So what I want is anything between GG-94B% and GG-05C%
GG-94B233
GG-03A434
GG-05B555
GG-05C343
Thanks in advance. Using Microsoft SQL Server Management Studio
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Write a query:
SELECT *
FROM TableNameHere
WHERE ColumnName >= 'GG-94B' AND ColumnName < 'GG-05D' --> need to be D because is not included (minor than)
WHERE MyFiled >= @LowVal + '%' AND MyFiled <= @HighVal + '%';
If you want to specify @LowVal (or @HighVal ) only and leave the other blank to be ignored:
WHERE MyFiled >= iif(@LowVal='', MyFiled , @LowVal + '%') AND MyFiled <= iif(@HighVal='', MyFiled , @HighVal + '%');
WHERE MyFiled >= iif(@LowVal='', MyFiled , @LowVal + '%') AND MyFiled <= iif(@HighVal='', MyFiled , @HighVal + '%');
Looks like the question might be answered above, but if not this scenario works dumping these values into a temp table with an identity field, querying for the first GG-94B% and GG-05C% values, and then selecting between the two..
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
DROP TABLE #tmp
CREATE TABLE #tmp (val varchar(25), row_number int identity(1,1))
INSERT INTO #tmp (val)
VALUES
('GG-93A123')
,('GG-94A222')
,('GG-94B233')
,('GG-03A434')
,('GG-05B555')
,('GG-05C343')
,('GG-05F433')
,('GG-06A665')
Declare @min int, @max int
SELECT @min = min(row_number) FROM #tmp WHERE val LIKE 'GG-94B%'
SELECT @max = min(row_number) FROM #tmp WHERE val LIKE 'GG-05C%'
SELECT * FROM #tmp WHERE row_number BETWEEN @min and @max
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Here is the solution I came up with:
Where Item BETWEEN 'GG-0%' AND 'GG-05D%' OR Item bETWEEN 'GG-94A%' AND 'GG-99Z%'
Where Item BETWEEN 'GG-0%' AND 'GG-05D%' OR Item bETWEEN 'GG-94A%' AND 'GG-99Z%'
ASKER
Hope this is alright but the reason I split the answer is because Phillip Burton made me realize I was setting up the range wrong and when I saw Ahmed Merghani response made me realize I could use the between to setup the range.
Solution:
Where Item BETWEEN 'GG-0%' AND 'GG-05D%' OR Item bETWEEN 'GG-94A%' AND 'GG-99Z%'
Solution:
Where Item BETWEEN 'GG-0%' AND 'GG-05D%' OR Item bETWEEN 'GG-94A%' AND 'GG-99Z%'
Why are those answers the solution? They return nothing. Even yours does not match the desired result..
DECLARE @Sample TABLE ( Data VARCHAR(255) );
INSERT INTO @Sample
VALUES ( 'GG-93A123' ),
( 'GG-94A222' ),
( 'GG-94B233' ),
( 'GG-03A434' ),
( 'GG-05B555' ),
( 'GG-05C343' ),
( 'GG-05F433' ),
( 'GG-06A665' );
SELECT *
FROM @Sample
WHERE Data BETWEEN 'GG-0%' AND 'GG-05D%'
OR Data bETWEEN 'GG-94A%' AND 'GG-99Z%'
/*
(8 row(s) affected)
Data
---------
GG-94A222
GG-94B233
GG-03A434
GG-05B555
GG-05C343
(5 row(s) affected)
*/
On the same point:
substring(MyField,1,6)>='GG-94B' <-- here assumption is made your entry will be always 6-digit long, but it could vary, right?
substring(MyField,1,6)>='GG-94B' <-- here assumption is made your entry will be always 6-digit long, but it could vary, right?
ASKER
Eghtebas, It will always be longer. I tried using the substring suggestion but because of the range issue (the two numbers represent year), couldn't get it to work. I see now I left out that important detail. the number is the year. 95 = 1995.
Now your problem makes sense 😉 You need to build a four digit year, then it should work.
Also expand your boundary values then BETWEEN works.
DECLARE @Sample TABLE ( Data VARCHAR(255) );
INSERT INTO @Sample
VALUES ( 'GG-93A123' ),
( 'GG-94A222' ),
( 'GG-94B233' ),
( 'GG-03A434' ),
( 'GG-05B555' ),
( 'GG-05C343' ),
( 'GG-05F433' ),
( 'GG-06A665' );
SELECT *,
SUBSTRING(Data, 1, 3) +
CASE WHEN CAST(SUBSTRING(Data, 4, 2) AS INT) < 90 THEN '19' ELSE '20' END+
SUBSTRING(Data, 4, 6) AS Expanded
FROM @Sample
Also expand your boundary values then BETWEEN works.
You can avoid casting to integer (that might produce an error) by just using string functions. e.g.
select
*
from table1
where left(replace(replace(item,'GG-9','199'),'GG-0','200'),5)
between '1994B' and '2005C'
| ITEM |
|-----------|
| GG-94B233 |
| GG-03A434 |
| GG-05B555 |
| GG-05C343 |
CREATE TABLE Table1
([ITEM] varchar(9))
;
INSERT INTO Table1
([ITEM])
VALUES
('GG-93A123'),
('GG-94A222'),
('GG-94B233'),
('GG-03A434'),
('GG-05B555'),
('GG-05C343'),
('GG-05F433'),
('GG-06A665')
;
http://sqlfiddle.com/#!3/44d22/4
Open in new window