Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 267
  • Last Modified:

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
0
MadIce
Asked:
MadIce
  • 3
  • 3
  • 2
  • +5
2 Solutions
 
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
 
Ahmed MerghaniCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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 MerghaniCommented:
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
 
PortletPaulCommented:
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 3
  • 2
  • +5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now