CODEWITHSUNDEEP
sqlhiveqlwildcard
Vijay Shekhawat
Vijay Shekhawat

Reputation: 159

How can I use a list of wildcard match in SQL?

I have a SQL code :

       CASE
     WHEN job_title LIKE '%Temp%'
           OR job_title LIKE '%Intern%'
           OR job_title LIKE '%Substitute%' THEN true
     ELSE false
   END   AS is_part_time,

I wanted to know if there is a cleaner way to do this, is it possible to use a list of wildcards similar to IN clause? Something like this?

       CASE
     WHEN job_title LIKE ['%Temp%','%Intern%','%Substitute%'] THEN true
     ELSE false
   END   AS is_part_time,

Upvotes: 0

Views: 148

Answers (2)

ggordon
ggordon

Reputation: 10035

You may consider using regular expressions and simplify this using RLIKE eg

 CASE
     WHEN job_title RLIKE '.*(Temp|Intern|Substitute).*' THEN true
     ELSE false
 END   AS is_part_time,

or simply

job_title RLIKE '.*(Temp|Intern|Substitute).*' AS is_part_time

Let me know if this works for you.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270311

You can phrase this using regular expressions:

(job_title rlike 'Temp|Intern|Substitute') as is_part_time

Note: You should really have a table with valid job titles. That table should have columns that specify characteristics of the job, such as is_part_time.

Upvotes: 0

Related Questions