CODEWITHSUNDEEP
sqlpostgresqlsql-like
arty
arty

Reputation: 669

How to use values from columns with LIKE - Postgresql

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

[email protected]

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 |
+-----------+----------+-----------------------------+-------+

Upvotes: 0

Views: 58

Answers (1)

The Impaler
The Impaler

Reputation: 48780

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

Upvotes: 2

Related Questions