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?
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]