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

Question

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:

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

Please provide sample output, what you want the query to return as a result. — The_DemoCorgin, Jun 17 '14 at 21:14
So you are looking for columns which don't have integers in them? What about *123.23* or *$2,302* — TheNorthWes, Jun 17 '14 at 21:22
@AdmiralAdama 123.23 should be listed in the result. For my scenario that is not a number alone. I will update the sample in the question. — LCJ, Jun 17 '14 at 21:25

score 2 · Accepted Answer · answered Jun 17 '14 at 21:23

2

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

answered Jun 17 '14 at 21:23

Chipmonkey

863
7
18

Why did this get downvoted? I thought it worked... I edited to elaborate a bit. – Chipmonkey Jun 17 '14 at 21:28
I didn't... there's no orange arrow on the question, but I'll be glad to upvote it to counter; I think it's an OK question. – Chipmonkey Jun 17 '14 at 21:31
OP Stated he can't match punctuation – TheNorthWes Jun 17 '14 at 21:33
1

I thought he stated he DID want punctuation: "123.23 should be listed in the result" was OP's quote. This returns both "1.2" and "$1", which I still think is the request. – Chipmonkey Jun 17 '14 at 21:36
2

Ahh sorry when I scanned your answer I got the logic on matching mixed up. I think this is the answer +1 – TheNorthWes Jun 17 '14 at 21:38

score 0 · Answer 2 · answered Jul 20 '16 at 20:05

0

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

answered Jul 20 '16 at 20:05

jai

11

1

try explaining and elaborating on your query – fk2 Jul 20 '16 at 20:12

score -1 · Answer 3 · answered Jun 17 '14 at 21:16

-1

Use IsNumeric

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

answered Jun 17 '14 at 21:16

gh9

10,169
10
63
96

1

`isNumeric` returns 1 if the number is convertible to a number. It doesn't scan for a number in a string. – TheNorthWes Jun 17 '14 at 21:18
2

`IsNumeric` accepts monetary amounts like `$1,000.00`. – Dour High Arch Jun 17 '14 at 21:18
OP just wants to see if a column is a number or a mix of characters. so in this case IsNumeric will give the needed value stating that the column is only a number or not – gh9 Jun 17 '14 at 21:19
Instead of a subquery why don't you just do `WHERE ISNUMERIC(workorders) != 1` ? – Michael McGriff Jun 17 '14 at 21:21
@michealmgriff ops edit came in mid writing of answer, i just havent changed it yet – gh9 Jun 17 '14 at 21:23

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

3 Answers3

Linked