SQL query dont work correct in postgresql database

Question

I have a simple SQL query where help me to find it the values have some English letter in text of field.

here the SQL :

select id,field1 
from mytable 
where mytable like '[A-Z]'

that query work nice in MS-ACCESS database but not work on DB MANAGER on QGIS and postgis query because I think Postgres doesn't recognize the list of alphabet [A-Z].

if I use this :

select id,field1 
from mytable 
where mytable like '%A%' 
   or mytable like '%B%'......

then it works but I don't like this method.

In Postgres:

select public.mytable.id,public.mytable.field1 
from mytable 
where public.mytable.mytable like '[A-Z]'

I try to use :

'*[A-Z]*'
'%[A-Z]%'

but that didn't change anything

Maybe you have a table that is yours and you named it mytable but MySQL is not yours and it is not the same thing (or even related to) PostgreSQL. — axiac
– axiac, Commented Oct 5, 2017 at 18:55
Removed mysql tag. I think Stack Overflow adds that tag by default to all SQL questions. >:-( meta.stackoverflow.com/questions/275170/… — Bill Karwin
– Bill Karwin, Commented Oct 5, 2017 at 19:04
as documented in the manual LIKE does not support regular expressions — user330315
– user330315, Commented Oct 5, 2017 at 19:34

Mureinik · Accepted Answer · 2017-10-05 18:49:26Z

6

You can use the ~ operator to evaluate a regex:

SELECT id, field1 FROM mytable WHERE mytable ~ '[A-Z]'

answered Oct 5, 2017 at 18:49

Mureinik

316k54 gold badges400 silver badges405 bronze badges

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

Comments

millevi · Accepted Answer · 2017-10-05 18:56:35Z

3

In postgres there is the tilde operator for matching strings to regexes

select * from some_table where some_column ~ '^[A-Z]$'

will select records where some_column consists of only uppercase letters. Also ~* is for case-insensitive comparisons.

answered Oct 5, 2017 at 18:56

millevi

1161 silver badge4 bronze badges

2 Comments

Mar Over a year ago

that is only for first letter

millevi Over a year ago

Yes, only the first. If you need arbitrary number of letters then use [A-Z]+ and if you want to match a string that has uppercase letters in it but might contain other characters as well then remove ^ and $ from the regex

Collectives™ on Stack Overflow

SQL query dont work correct in postgresql database

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related