Solved

Query range with wildcard criteria

Posted on 2014-11-07
14
264 Views
Last Modified: 2014-11-07
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
0
Comment
Question by:MadIce
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +5
14 Comments
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 250 total points
ID: 40428784
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
 
LVL 8

Expert Comment

by:Ahmed Merghani
ID: 40428785
Try this query:
select * from YOUR_TABLE where FIELD_NAME like 'GG-94B%' or FIELD_NAME like  'GG-05C%'

Open in new window

0
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40428788
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 40428794
WHERE MyFiled >=  @LowVal + '%' AND  MyFiled <= @HighVal + '%';
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 40428800
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
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40428812
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
 
LVL 8

Assisted Solution

by:Ahmed Merghani
Ahmed Merghani earned 250 total points
ID: 40428822
Sorry I update my solution:
select * from YOUR_TABLE where FIELD_NAME BETWEEN 'GG-94B%' and  'GG-05C%'

Open in new window

0
 

Author Comment

by:MadIce
ID: 40428982
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
 

Author Closing Comment

by:MadIce
ID: 40428993
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
 
LVL 34

Expert Comment

by:ste5an
ID: 40429020
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
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 40429085
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
 

Author Comment

by:MadIce
ID: 40429346
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
 
LVL 34

Expert Comment

by:ste5an
ID: 40429380
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40429714
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

Featured Post

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question