CODEWITHSUNDEEP
sqlsql-server
LCJ
LCJ

Reputation: 22662

how to query a table to find rows where there are no numbers in a column

I have a column that has numbers and characters in SQL Server 2005

Note: Here character means anything other than a number.

I need to list all records that has a character in it (other than numbers). Following query will list a record if there are no numbers in a record. But I need to list all records that has characters in it irrespective of whether it has number or not.

How to modify this query to return all records that has atlesat one character?

Reference:

  1. How would I determine if a varchar field in SQL contains any numeric characters?
  2. SQL query for finding rows with special characters only

CODE

DECLARE @WorkOrderHistory TABLE (WorkOrder VARCHAR(10))
INSERT INTO @WorkOrderHistory (WorkOrder) VALUES ('123456') --EXCLUDE from result
INSERT INTO @WorkOrderHistory (WorkOrder) VALUES ('abvc@@@') --Need to list in result
INSERT INTO @WorkOrderHistory (WorkOrder) VALUES ('a+bvc1234') --Need to list in result
INSERT INTO @WorkOrderHistory (WorkOrder) VALUES ('++') --Need to list in result
INSERT INTO @WorkOrderHistory (WorkOrder) VALUES ('$1') --Need to list in result
INSERT INTO @WorkOrderHistory (WorkOrder) VALUES ('1.2')  --Need to list in result

--Any record without a number
SELECT TOP 10 *
FROM @WorkOrderHistory WO  
WHERE WO.WorkOrder NOT like '%[0-9]%'

Upvotes: 1

Views: 3678

Answers (3)

jai
jai

Reputation: 11

select * from sales where name not like '%[0-9]%'

Upvotes: 0

Chipmonkey
Chipmonkey

Reputation: 865

The carat (^) inside a [] bracket means match any character except for those in the list. So if you want to match any character except for 0-9, you use [^0-9] and the LIKE (without the NOT) will match all other characters.

This should work:

SELECT TOP 10 *
FROM @WorkOrderHistory WO  
WHERE WO.WorkOrder LIKE '%[^0-9]%'

Of course, you'll match all punctuation, and unprintable characters as well.

Via http://technet.microsoft.com/en-us/library/ms174214(v=sql.110).aspx

Upvotes: 2

gh9
gh9

Reputation: 10703

Use IsNumeric

SELECT * FROM (
    Select isNumeric(workorders) as isNumber,WorkOrder from table @workorderhistory) AS BOB
WHERE BOB.isNumber = 0

Upvotes: -1

Related Questions