New column based on list of values SQL

Question

I am new to SQL and working on a database that needs a binary indicator based on the presence of string values in a column. I'm trying to make a new table as follows:

Original:

Indicator
a, b, c
c, d, e

Desired:

Indicator	type
a, b, c	1
c, d, e	0

SQL code:

SELECT 
      ID, 
      Contract, 
      Indicator,
      CASE 
         WHEN Indicator IN ('a', 'b')
         THEN 1
         ELSE 0
      END as Type
INTO new_table
FROM old_table

The table I keep creating reports every type as 0.

I also have 200+ distinct indicators, so it will be really time-consuming to write each as:

CASE 
   WHEN Indicator = 'a' THEN '1'
   WHEN Indicator = 'b' THEN '1'

Is there a more streamlined way to think about this?

Thanks!

Answer 1

I think the first step is to understand why your code doesn’t work right now.

If your examples of what’s Indicator column are literally the strings you noted (a, b, c in one string and c, d, e in another) you should understand that your case statement is saying “I am looking for an exact match on the full value of Indicator against the following list -

1. The letter A or
2. The letter B

Essentially- you are saying “hey SQL, does ‘a,b,c’ match to ‘a’? Or does ‘a,b,c’ match to ‘b’. ?”

Obviously SQL’s answer is “these don’t match” which is why you get all 0s.

You can try wildcard matching with the LIKE syntax.

Case when Indicator like ‘%a%’ or Indicator like ‘%b%’ then 1 else 0 end as Type

Now, if the abc and cde strings aren’t REALLY what’s in your database then this approach may not work well for you.

Example, let’s say your real values are words that are all slapped together in a single string.

Let’s say that your strings are 3 words each.

1. Cat, Dog, Man
2. Catalog, Stick, Shoe
3. Hair, Hellcat, Belt

And let’s say that Cat is a value that should cause Type to be 1.

If you write: case when Indicator like ‘%cat%’ then 1 else 0 end as Type - all 3 rows will get a 1 because the wildcard will match Cat in Catalog and cat in Hellcat.

I think the bottom line is that unless your Indicator values really are 3 letters and your match criteria is a single letter, you very well could be better off writing a 200 line long case statement if you need this done any time soon.

A better approach to consider (depending on things like are you going to have 300 different combinations a week or month or year from now?)

If yes, wouldn’t it be nice if you had a table with a total of 6 rows - like so?

Indicator | Indictor_Parsed

a,b,c | a

a,b,c | b

a,b,c | c

c,d,e | c

c,d,e | d

c,d,e | e

Then you could write the query as you have it case when Indicator_Parsed in (‘a’, ‘b’) then 1 else 0 end as Type - as a piece of a more verbose solution.

If this approach seems useful to you, here’s a link to the page that lets you parse those comma-separated-values into additional rows. Turning a Comma Separated string into individual rows

Answer 2

ON mysql/sql server You can do it as follows :

insert into table2
select Indicator,
  CASE WHEN Indicator like '%a%' or Indicator like '%b%' THEN 1 ELSE 0 END As type
from table1;

demo here

Answer 3

You can use the REGEXP operator to check for presence of either a, b or both.

SELECT Indicator,
       Indicator REGEXP '.*[ab].*'
FROM tab

If you need that into a table, you either create it from scratch

CREATE your_table AS 
SELECT Indicator,
       Indicator REGEXP '.*[ab].*'
FROM tab

or you insert values in it:

INSERT INTO your_table
SELECT Indicator,
       Indicator REGEXP '.*[ab].*'
FROM tab

Check the demo here.