MS SQL

MS SQL RSS

SELECT statement

Please support our MS SQL advertiser: Intel Parallel Studio Home

•

Join Date: Jun 2005

Posts: 92

Reputation: michael123 is an unknown quantity at this point

Solved Threads: 0

michael123 michael123 is offline

Offline

Junior Poster in Training

SELECT statement

Oct 16th, 2009

I have a table for example: tb1
with data:

Help with Code Tags

MS SQL Syntax (Toggle Plain Text)

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?

Last edited by michael123; Oct 16th, 2009 at 8:41 pm.

•

Join Date: Feb 2009

Posts: 3,251

Reputation: sknake has much to be proud of

Solved Threads: 578

sknake

Offline

.NET Enthusiast

Oct 17th, 2009

Try this:

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
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]%'
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]%'