Regex Syntax Misuse

Question

I am running this query:

Select column_name
from table
where column_name ~ '%[A-Za-z]%'
group by column_name

but I am not getting any results. What am I doing wrong?

Goal: This is a column that includes phone numbers. I am trying to find any values that contain string characters.

I don't understand why ilike does not support regex This is what I found here

The operator ~~ is equivalent to LIKE, and ~~* corresponds to ILIKE. There are also !~~ and !~~* operators that represent NOT LIKE and NOT ILIKE, respectively. All of these operators are PostgreSQL-specific.

Doesn't this mean I can use ilike by using ~~*?

Edit: What I have learned so far

Don't use like, use tilda.
Where column_name ~ '%[A]%'
Where column_name ~ $$[A]$$ does work.

Theory: It has something to with the dollar signs or the apostrophe. Result: It was the % signs.

ILIKE doesn't support regex - it only supports the wildcards that LIKE supports. If you want regex use the ~ operator. See the manual for details: postgresql.org/docs/current/static/… — user330315
– user330315, Commented Oct 6, 2016 at 15:04

Tim Pietzcker · Accepted Answer · 2016-10-06 15:05:38Z

1

According to RegexBuddy, the correct syntax for the WHERE clause is:

WHERE mycolumn ~ $$[A-Z]$$

answered Oct 6, 2016 at 15:05

Tim Pietzcker

337k59 gold badges520 silver badges572 bronze badges

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

2 Comments

Chicken Sandwich No Pickles Over a year ago

It worked!!!! I typed the query incorrectly from what you gave me, my mistake. I left the % signs in.

Laurenz Albe Over a year ago

If lower case characters are an issue, use ~ '[A-Za-z]' to find anything that contains non-digits, use ~ '[^0-9]'

Collectives™ on Stack Overflow

Regex Syntax Misuse

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related