Lijo Lijo - 4 months ago 8
SQL Question

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]%'

Answer

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