Email addresses not showing in query when using LIKE '_@_%' - POSTGRE SQL

Question

I'm cleaning some user data and am trying to find any emails that were input into the "First Name" column. Ideally, I would run a simple function and any field with "@" in it will appear.

The code I'm using to find emails is

SELECT * FROM "Import File"
WHERE "First Name" LIKE '_@_%';

I got 0 results, so I tried

WHERE "First Name" LIKE '_@_%'
OR "First Name" LIKE '__@_%' 
OR "First Name" LIKE '___@_%'

and so forth.

The problem is I get a different number of results for each OR LIKE and typing it out every time will be tedious. Is there a faster function for this? (other than copy pasting)

You want to use the any characters wildcard twice, like `LIKE '%@%'`? — sticky bit, Jan 17 '19 at 22:43
Have you read [the documentation for `LIKE`](https://dev.mysql.com/doc/refman/5.7/en/pattern-matching.html)? It explains how to use it... — Lightness Races in Orbit, Jan 17 '19 at 22:47

score 1 · Accepted Answer · answered Jan 17 '19 at 22:45

1

In LIKE operations, the underscore character (_) is a single-character wildcard, whereas the percent character (%) is a multi-character wildcard.

So, to find if a column has an at symbol, you could just use this logic:

WHERE "First Name" LIKE '%@%'

...or...

WHERE POSITION('@' IN "First Name") > 0

answered Jan 17 '19 at 22:45

e_i_pi

4,590
4
27
45

Thanks for this! Very helpful – Alexander Luong Jan 18 '19 at 20:30

Email addresses not showing in query when using LIKE '_@_%' - POSTGRE SQL

1 Answers1