CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
Roshan
Roshan

Reputation: 58

How to check for a substring match along with conditional expression?

I have a table that is a combination of two tables.I want to First check if Keyword is in Names.

**Second Part**

Check if Supplier1 is empty or not, If yes then write "YES", else check if Supplier equals Supplier1, If yes then write "2*Yes" else do nothing!!

Table:

| Names                | Supplier | Company1  |Keyword |SUPPLIER1  | BRANDS  |
| -------------------- | ---------|-----------|---------|----------|--------|
| ADIDAS PREDATOR 17.1 |ADIDAS    | <NA>      |PREDATOR |<NA>      |        |
| NIKE MERCURIAL 2020  |NIKE      | <NA>      |PREDATOR |          |        | 
| NIKE VAPOR 2021      |NIKE      | <NA>      |PREDATOR |          |        |
| NEW BALANCE FURON    |JD        | <NA>      |PREDATOR |          |        |
| PUMA RAPIDO 21.3     |STADIUM   | <NA>      |PREDATOR |          |        |
| ADIDAS PREDATOR 17.1 |ADIDAS    | <NA>      |PREDATOR |ADIDAS    |        |
| ADIDAS PREDATOR 17.1 |ADIDAS    | <NA>      |PREDATOR |JD        |        |

**Output**

| Names                | Supplier | Company1  |Keyword |SUPPLIER1 | BRANDS |
| -------------------- | ---------|-----------|---------|----------|--------|
| ADIDAS PREDATOR 17.1 |ADIDAS    | <NA>      |PREDATOR |<NA>      |Yes     |
| NIKE MERCURIAL 2020  |NIKE      | <NA>      |PREDATOR |          |        |
| NIKE VAPOR 2021      |NIKE      | <NA>      |PREDATOR |          |        |
| NEW BALANCE FURON    |JD        | <NA>      |PREDATOR |          |        |
| PUMA RAPIDO 21.3     |STADIUM   | <NA>      |PREDATOR |          |        |
| ADIDAS PREDATOR 17.1 |ADIDAS      | <NA>      |PREDATOR |ADIDAS    |2*Yes    |
| ADIDAS PREDATOR 17.1 |ADIDAS    | <NA>      |PREDATOR |JD        |         |

This will do the first part but I cannot figure out the second part(tried using CASE):

    SELECT NAMES,BRANDS 
    FROM TABLE 
    WHERE NAMES LIKE '%' || KEYWORD|| '%';

Upvotes: 0

Views: 36

Answers (1)

AGVG
AGVG

Reputation: 26

Case when or if else if should work fine in this case

select Names,Supplier, Company1,Keyword,SUPPLIER1
,case when SUPPLIER1 = Supplier then "2*Yes" 
      when SUPPLIER2 is not null then "Yes"
else null end as BRANDS
FROM TABLE

Upvotes: 1

Related Questions