CODEWITHSUNDEEP
sqlsql-servert-sql
Rakesh
Rakesh

Reputation: 2790

sql skip rows that contains string

Hi I need to skip rows that may contain text

Example: I have table OUTSTANDING and my fields are given below.

INVOICENO 
AB1       
111
ZX3

I need to skip rows contain AB1 and ZX3 and select the row that contain 111

I have tried this query

"SELECT * FROM [INT]..OUTSTANDING WHERE INVOICENO NOT LIKE '%[a-z,-]%'";

Upvotes: 5

Views: 2445

Answers (4)

mehdi lotfi
mehdi lotfi

Reputation: 11591

Try following mothods:

Method 1:

SELECT * 
FROM [INT]..OUTSTANDING
WHERE InvoiceNo IS NOT NULL
    AND ISNUMERIC(InvoiceNo)=1

Mehod 2:

SELECT * 
FROM [INT]..OUTSTANDING 
WHERE InvoiceNo IS NOT NULL
  AND INVOICENO NOT LIKE '%[^0-9]%'

Upvotes: 0

Mihai
Mihai

Reputation: 26794

WHERE ISNUMERIC(INVOICENO) = 1

http://technet.microsoft.com/en-us/library/ms186272.aspx

Upvotes: 0

Milen
Milen

Reputation: 8877

try with: 

"SELECT * FROM [INT]..OUTSTANDING WHERE NOT INVOICENO LIKE '%[0-9]%'";

or 

...where INVOICENO not like '%[a-z]%'

unless more advanced regex is required....
source: http://www.sqllion.com/2010/12/pattern-matching-regex-in-t-sql/

Upvotes: 1

Nelly
Nelly

Reputation: 522

Select *
FROM [INT]..OUTSTANDING 
WHERE IsNumeric(INVOICENO ) = 1 
And INVOICENO Is Not NULL

Upvotes: 1

Related Questions