sqlstringsql-likecase-statement

How do I print the matched pattern that produced the result of a multi-like case statement?


I am looking to store in a column (matched_pattern), as a string value, the matched pattern for a string field (title) from a multi-like case when statement in SQL.

Example table:


|      Title          |     Email        |
|---------------------|------------------|
|         CEO         | henry@test.com   |
|---------------------|------------------|
|         COO         | julien@test.com  |
|---------------------|------------------| 

The query I have is

select 
title,
email,
CASE WHEN lower(title) LIKE     '%cco%' OR
lower(title) LIKE '%ceo%' THEN 'CxO' ELSE null END persona
FROM table

The result I want is


|      Title |     Email        | Persona |matched_pattern  |
|------------|------------------|---------------------------|
|  CEO       | henry@test.com   | CxO     | '%ceo%'         |
|------------|------------------|---------|-----------------|
|  CcO       | julien@test.com  | CxO     | '%cco%'         |
|------------|------------------|---------|-----------------|

Is there a way of storing the code that generates the result in a separate column? I have used another case statement to create the matched_pattern column

SELECT
title,
CASE WHEN lower(title) LIKE     '%cco%' THEN 'CxO'
CASE WHEN lower(title) LIKE     '%ceo%' THEN 'CxO'
ELSE null END persona,
CASE WHEN lower(title) LIKE     '%cco%' THEN '%cco%'
CASE WHEN lower(title) LIKE     '%ceo%' THEN '%ceo%'
ELSE null END matched_pattern
FROM table

Is there a better way to do this?


Solution

  • If you materialize the patterns in another table or temp table or something, you can simply do this:

    SELECT *
    FROM DataSource DS
    LEFT JOIN Paterns P 
        ON DS.[Title] LIKE P.[patern]
    

    Here is full working example on SQL Server:

    CREATE TABLE DataSource
    (
        [Title] VARCHAR(12)
       ,[Email] VARCHAR(32)
    );
    
    CREATE TABLE Paterns
    (
        [patern] VARCHAR(12)
       ,[value] VARCHAR(12)
    );
    
    GO
    
    INSERT INTO DataSource ([Title], [Email])
    VALUES ('CEO', 'henry@test.com')
          ,('CEO', 'julien@test.com');
    
    INSERT INTO Paterns ([patern], [value])
    VALUES ('%ceo%', 'CxO')
          ,('%cco%', 'CxO')
    
    
    SELECT *
    FROM DataSource DS
    LEFT JOIN Paterns P 
        ON DS.[Title] LIKE P.[patern]
    

    enter image description here