Select SQL Non numeric Values

Question

I need to select just the rows within a sql table which contain no numerical values whatsoever, here's an example table:

AddressLine1
-------------
59 Prospect Road
Rose House
24 St. Pauls Place
1 Oxford Avenue
9 Stonecross Road
65 Wood Common
19 Falcon Close
Thorn House
16 Poplars Close
52 Coombes Road
12 Brinsmead
14 Meadow Close
15 Rowlatt Drive

In this example I would just be looking for "Rose House" and "Thorn House" rows to be returned.

Any suggestion on the code I should be using would be gratefully received.

You can consider using regular expressions, some examples you will find [here](https://stackoverflow.com/questions/8928378/using-regex-in-sql-server) on StackOverflow — TT., Jan 07 '16 at 10:33
Hi @adam-haycok , ah ... better formatting ... ah ... 2 thumbs up | pat in the back | high 5 — Andy K, Jan 07 '16 at 10:38
Some answers were given but the better option would be to split it up in two columns: AddressNumber and AddressName. — Carra, Jan 07 '16 at 10:39

score 23 · Accepted Answer · edited Jan 07 '16 at 10:44

23

select * from tab
where AddressLine1 not like '%[0-9]%'

try this

edited Jan 07 '16 at 10:44

Carsten Massmann

26,510
2
22
43

answered Jan 07 '16 at 10:32

Akshey Bhat

8,227
1
20
20

You are Welcome @Adam – Akshey Bhat Feb 23 '16 at 16:10
Using this query I am not able to get non numeric values that have an alpha character in the middle of the value – Sandhya Jul 23 '20 at 18:34

score 9 · Answer 2 · answered May 20 '21 at 15:44

9

I think the actual answer should be:

select * from tab where AddressLine1 like '%[^0-9]%'

According to: https://learn.microsoft.com/en-us/sql/t-sql/language-elements/like-transact-sql?view=sql-server-ver15

answered May 20 '21 at 15:44

INS

10,594
7
58
89

score 0 · Answer 3 · answered Mar 28 '22 at 11:29

0

This is the way:

SELECT * FROM TABLE_NAME WHERE NOT REGEXP_LIKE(COLUMN_NAME, '^-?[0-9.]+$');

This also excludes values which contain decimals.

answered Mar 28 '22 at 11:29

Indru

3
1

Select SQL Non numeric Values

3 Answers3