How to use values from columns with LIKE - Postgresql

Question

I am trying to check the validity of a list of emails. Valid emails are those that are formatted as:

I can find the valid emails using regex or LIKE operator. But I would like to learn if there is a way to use the data from other columns such as 'name' and 'lastname' to avoid any problems that can arise from unique names that include different characters that isn't covered by [A-Za-z] in Regex.

I've tried this:

SELECT name, 
       lastname, 
       email, 
       CASE
           WHEN email ~ '[A-Za-z]*\.[A-Za-z]*\@[A-Za-z]*\.[A-Za-z]*' THEN true
           ELSE false
       END AS valid
FROM personlist

and this:

SELECT name, 
       lastname, 
       email, 
       CASE 
         WHEN email LIKE '%.%||%.%' THEN true 
         ELSE false 
       END AS valid 
FROM   personlist

They seem to work just fine but I want to learn if there is a way to use the data from the other columns with an operator such as LIKE. I think using the data from existing columns would result in more targeted and solid queries.

For example:

+-----------+----------+-----------------------------+-------+
|   name    | lastname |            email            | valid |
+-----------+----------+-----------------------------+-------+
| Molly-Rae | Jackson  | [email protected] | true  |
| Molly-Rae | Jackson  | [email protected]  | false |
+-----------+----------+-----------------------------+-------+

Also I need to disregard the case sensitivity and delete any spaces in names/lastnames. Would ILIKE and REPLACE be enough? I can't try them since I can't use the data from the columns with LIKE operator. — arty
– arty, Commented Nov 7, 2019 at 0:41
Your question is unlcear, I think this is what you're asking. Do you know host and domain? Then you can do a direct comparison: CASE WHEN name || '.' || lastname || '@' || host || '.' || domain = email THEN TRUE ELSE FALSE END. If you don't know host & domain, then you can split the email on '@' and do a direct comparison between the name, lastname and the left portion, and a regex on the right half (just make sure that count of '.' > 0?) — Kevin Postlewaite
– Kevin Postlewaite, Commented Nov 7, 2019 at 0:41
I don't know the domain. I only know the data in columns name and lastname. — arty
– arty, Commented Nov 7, 2019 at 0:43
Unrelated, but: your CASE expression, can be simplified to email ~ '[A-Za-z]*\.[A-Za-z]*\@[A-Za-z]*\.[A-Za-z]*' as valid to do a case insensitive matching use ~* instead of ~ — user330315
– user330315, Commented Nov 7, 2019 at 6:57

The Impaler · Accepted Answer · 2019-11-07 01:12:36Z

2

You can use the other columns to compute the pattern you want to validate against. For example:

select
  name, 
  lastname, 
  email,
  lower(email) like lower(name || '.' || lastname || '@xmail.com') as valid
from personlist

answered Nov 7, 2019 at 1:12

The Impaler

49.3k10 gold badges50 silver badges90 bronze badges

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

Collectives™ on Stack Overflow

How to use values from columns with LIKE - Postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related