Solved

Query range with wildcard criteria

Posted on 2014-11-07
14
253 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 45

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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 32

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 32

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

706 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

20 Experts available now in Live!

Get 1:1 Help Now