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 on StackOverflow — TT.
– TT., Commented Jan 7, 2016 at 10:33
Hi @adam-haycok , ah ... better formatting ... ah ... 2 thumbs up | pat in the back | high 5 — Andy K
– Andy K, Commented Jan 7, 2016 at 10:38
Some answers were given but the better option would be to split it up in two columns: AddressNumber and AddressName. — Carra
– Carra, Commented Jan 7, 2016 at 10:39

Carsten Massmann · Accepted Answer · 2016-01-07 10:44:40Z

32

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

try this

edited Jan 7, 2016 at 10:44

Carsten Massmann

28.4k3 gold badges25 silver badges49 bronze badges

answered Jan 7, 2016 at 10:32

Akshey Bhat

8,6872 gold badges23 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Akshey Bhat Over a year ago

You are Welcome @Adam

Sandhya Over a year ago

Using this query I am not able to get non numeric values that have an alpha character in the middle of the value

INS · Accepted Answer · 2021-05-20 15:44:24Z

12

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, 2021 at 15:44

INS

10.9k8 gold badges63 silver badges94 bronze badges

1 Comment

Don B Over a year ago

This actually was the answer I needed when looking this up, but just a heads up, based on the OP, I think he's looking for values that don't have any number in the text, this one will return values that have numbers in them. So, I think this one is perfect if you're looking for any value that is not a number, but for the OP I think he's looking for a string without any numbers in it.

Indru · Accepted Answer · 2022-03-28 11:29:05Z

-1

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, 2022 at 11:29

Indru

11 bronze badge

2 Comments

M.Vakili Jan 2 at 12:08

I think they talk about TSQL not Oracle

Indru Jan 3 at 17:24

This is not mentioned in the original post.

Collectives™ on Stack Overflow

Select SQL Non numeric Values

3 Answers 3

2 Comments

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related