0

I have a table for example: tb1
with data:

cse-12m
cse-343k
cse-mka
cse-ptu
cse-jpy

How can I write a SELECT statement to retrieve data in one of these formats:
1) cse-three digits number
2) cse-p%

and exclude other format data, any idea?

Edited by michael123: n/a

2
Contributors
1
Reply
2
Views
8 Years
Discussion Span
Last Post by sknake
0

Try this:

IF OBJECT_ID('tempdb..#Test', 'U') IS NOT NULL DROP TABLE #TEST
Create Table #Test
(
  RecordId int identity(1000, 1) PRIMARY KEY,
  Value varchar(100)
)
SET NOCOUNT ON
Insert Into #Test (Value) Values ('cse-12m')
Insert Into #Test (Value) Values ('cse-343k')
Insert Into #Test (Value) Values ('cse-mka')
Insert Into #Test (Value) Values ('cse-ptu')
Insert Into #Test (Value) Values ('cse-jpy')
Insert Into #Test (Value) Values ('cse-123')
SET NOCOUNT OFF

--#1 - Matches cse-NNN, Does NOT match cse-123k
Select *
From #Test
Where Value Like 'cse-[0-9][0-9][0-9]'

--#2 - Matches cse-NNN*, so anything beginning with "cse-NNN" is matched
Select *
From #Test
Where Value Like 'cse-[0-9][0-9][0-9]%'

--#3 - Invert #1
Select *
From #Test
Where Value Not Like 'cse-[0-9][0-9][0-9]'

--#4 - Invert #2
Select *
From #Test
Where Value Not Like 'cse-[0-9][0-9][0-9]%'
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.