sql query doesn't select the right value's I need

Question

I'm trying to only select the data with numbers in them (the table looks like this):

eteee 231
wrgrr
test 1
bioo 21
wee

with the query: SELECT address1 FROM ps_address WHERE address1 not like '%[^0-9]%';

but i get all the values right back.

@jarlh's comment does what you need I'm not sure why you had a 'not like' when you wanted a like with the numeric check. — Rich Campbell, Nov 21 '18 at 10:02
when i try it with like and not with 'not like' I got no return at all — , Nov 21 '18 at 10:14
Which dbms are you using? ("HeidiSQL is a useful and reliable tool designed for web developers using the popular _MySQL_ server, _Microsoft SQL_ databases and _PostgreSQL_."...) — jarlh, Nov 21 '18 at 10:17
And the webshop database server is one of "_MySQL server, Microsoft SQL databases and PostgreSQL_" - which one? — jarlh, Nov 21 '18 at 10:23

score 2 · Answer 1 · answered Nov 21 '18 at 10:03

2

You can simply write:

SELECT address1 FROM ps_address WHERE address1 like '%[0-9]%';

answered Nov 21 '18 at 10:03

iminiki

when i try that query I get no return what so ever – Nov 21 '18 at 10:11
Are you sure the column's name is address1? – iminiki Nov 21 '18 at 10:14
Remember, my query does not include " ^ " as your original one. – iminiki Nov 21 '18 at 10:20

Nikhil S · Answer 2 · 2018-11-21T10:04:49.043

0

this will work :

SELECT address1 FROM ps_address WHERE regexp_like(address1,'.*[0-9]+.*');

edited Nov 21 '18 at 10:04

answered Nov 21 '18 at 09:55

Nikhil S

When i try that query I get the error that the regexp_like function doesn't exicst – Nov 21 '18 at 10:13
its all working in my db i dont know what is heideisql but for orcale i ts working – Nikhil S Nov 21 '18 at 10:32

score 0 · Accepted Answer · answered Nov 21 '18 at 10:26

0

Try this REGEXP:

SELECT address1 FROM ps_address WHERE address1 REGEXP '[[:digit:]]';

This returns the rows containing digits anywhere inside address1.

answered Nov 21 '18 at 10:26

forpas

3 Answers3