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
MadIceAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
If that were a valid range, then you could use

where substring(MyField,1,6)>='GG-94B' and substring(MyField,1,6)<='GG-05C'

But in the ASCII alphabet, '0' is before '9'. So it's like asking for a range between R and A. It's the wrong way round.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ahmed MerghaniSoftware EngineerCommented:
Try this query:
select * from YOUR_TABLE where FIELD_NAME like 'GG-94B%' or FIELD_NAME like  'GG-05C%'

Open in new window

0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Write a query:
SELECT *
FROM TableNameHere
WHERE ColumnName >= 'GG-94B' AND ColumnName < 'GG-05D' --> need to be D because is not included (minor than)

Open in new window

0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Mike EghtebasDatabase and Application DeveloperCommented:
WHERE MyFiled >=  @LowVal + '%' AND  MyFiled <= @HighVal + '%';
0
Mike EghtebasDatabase and Application DeveloperCommented:
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 + '%');
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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

Open in new window

0
Ahmed MerghaniSoftware EngineerCommented:
Sorry I update my solution:
select * from YOUR_TABLE where FIELD_NAME BETWEEN 'GG-94B%' and  'GG-05C%'

Open in new window

0
MadIceAuthor Commented:
Here is the solution I came up with:

Where Item BETWEEN 'GG-0%' AND 'GG-05D%' OR Item bETWEEN 'GG-94A%' AND 'GG-99Z%'
0
MadIceAuthor Commented:
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%'
0
ste5anSenior DeveloperCommented:
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)
*/

Open in new window

0
Mike EghtebasDatabase and Application DeveloperCommented:
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?
0
MadIceAuthor Commented:
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.
0
ste5anSenior DeveloperCommented:
Now your problem makes sense 😉 You need to build a four digit year, then it should work.

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 

Open in new window


Also expand your boundary values then BETWEEN works.
0
PortletPaulEE Topic AdvisorCommented:
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'

Open in new window

|      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

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.