CODEWITHSUNDEEP
sql-servert-sql
user3311323
user3311323

Reputation:

Removing letters (a-z) from select statement

i have a table as like this 

create table #test
 (id int ,  value varchar(15)  )

 insert into #test 
  values(1,'10')
  ,(2,'12')
  ,(3, '1.3')
  ,(4, 'NO VALUE')

  SELECT * FROM #TEST

when i selecting i don't want to see the record 4, i mean any records with the letters like (NO VALUE , NO DATA , (A-Z) ).Can some one help me here please.

Upvotes: 1

Views: 53

Answers (2)

Stephan
Stephan

Reputation: 6018

I guess pick whatever performs better for you. You could try something this:

SELECT *
FROM #test
WHERE     value != 'NO%'
      AND value NOT LIKE '[a-z]%'

Or like this:

SELECT  *
FROM #test
WHERE ISNUMERIC(value) = 1

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453608

To only return rows where value doesn't contain characters in the range A-Z

SELECT *
FROM   #test
WHERE  value NOT LIKE '%[A-Za-z]%' COLLATE LATIN1_GENERAL_BIN

Upvotes: 3

Related Questions