Solved

Query range with wildcard criteria

Posted on 2014-11-07
14
258 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
  • 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 46

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
 
LVL 33

Expert Comment

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

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 65

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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 33

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 33

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 33

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 48

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Creating and Managing Databases with phpMyAdmin in cPanel.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

867 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now