T- SQL Query to fetch values -Pattern Matching

Question

Post reply

T- SQL Query to fetch values -Pattern Matching

greeshatu

Right there with Babe

Points: 791
More actions
March 7, 2017 at 3:51 am

#335772

Create table StgTemp
(
ID nvarchar(50)
)
INSERT INTO StgTemp values ('0003005')
INSERT INTO StgTemp values ('03405')
INSERT INTO StgTemp values ('1003005')
INSERT INTO StgTemp values ('3003005')
INSERT INTO StgTemp values ('35003005')
INSERT INTO StgTemp values ('823005')
INSERT INTO StgTemp values ('9205')
INSERT INTO StgTemp values ('9205')
INSERT INTO StgTemp values ('9205')
INSERT INTO StgTemp values ('920')
INSERT INTO StgTemp values ('35556005')
INSERT INTO StgTemp Values ('3678909')
SELECT * FROM StgTemp
Desired Output:
SELECT '3553005'
UNION
SELECT '3678909'
UNION
SELECT '823005'
UNION
SELECT '9205'
UNION
SELECT '920'
ID contains numeric values prevailing zeros in some cases so it is defined as varchar
how to get values starts with 3555 to 3999 and 8000 to 9999.
There is no specific rule that length is always 4.
Eg: 35551 , 3568887867988, 3590909,8000, 85805667,
all of the values are valid and are to be fetched.
Please let me know T- SQL statement for the above scenario

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Cast to int and then it's simple to find values that fall in a certain range.

John

greeshatu Right there with Babe Points: 791 More actions · Answer 2

John Mitchell-245523 - Tuesday, March 7, 2017 3:54 AM
Cast to int and then it's simple to find values that fall in a certain range.
John

HI John if we cast 00003 will become 3 .I don't want to retrieve this value .It should start with 3 .
I have updated the question please check .

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

WITH ConverttoNumber AS ( SELECT ID , CAST(LEFT(ID + '000',4) AS int) AS Nbr FROM StgTemp ) SELECT DISTINCT ID FROM ConverttoNumber WHERE Nbr BETWEEN 3500 and 3999 OR Nbr BETWEEN 8000 and 9999

John

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 4

I'll might be missing something but I think that a simple select with range will do it:

SELECT distinct * FROM StgTemp where (ID >='3500' and ID <= '3999') or ID >='8000' and ID <= '9999'

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

greeshatu Right there with Babe Points: 791 More actions · Answer 5

I found a simple solution .. Thanks guys having a look at the query

SELECT ID FROM StgTemp WHERE ID LIKE '3[5-9]%' OR ID LIKE '[89]%'

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 6

greeshatu - Tuesday, March 7, 2017 4:30 AM
I found a simple solution .. Thanks guys having a look at the query
SELECT ID FROM StgTemp WHERE ID LIKE '3[5-9]%' OR ID LIKE '[89]%'

Your simple solution won't pick up things like '008900'. Should it?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)