Member Avatar for michael123

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 because: n/a
  • 2 Contributors
  • 1 Reply
  • 128 Views
  • 16 Hours Discussion Span
  • Latest Post Latest Post by sknake
Member Avatar for sknake

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]%'
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.