CODEWITHSUNDEEP
sql
user3440676
user3440676

Reputation: 43

Find out a strings location (columns) in SQL

Below is my SQL query to find out, do any of the columns (Req_ecCode1, Req_ecCode2, Req_ecCode3) have the word “YC”. Now my concern is there any way that I can know what is the column name does “YC” located at? Hope anyone can help me on this.

select *
from SC_Main
where Req_ecCode1 + Req_ecCode2 + Req_ecCode3 like ‘%YC%’

Upvotes: 3

Views: 65

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270653

I tend to concatenate the string names together:

select ((case when Req_ecCode1 like '%YC%' then 'Req_ecCode1;' else '' end) +
        (case when Req_ecCode2 like '%YC%' then 'Req_ecCode2;' else '' end) +
        (case when Req_ecCode3 like '%YC%' then 'Req_ecCode3;' else '' end)
       ) as columns_with_yc
from SC_Main
where Req_ecCode1 + Req_ecCode2 + Req_ecCode3 like '%YC%'

Note that your where conditions isn't really correct. If one code is 'Y' and the next 'C', then it will evaluate to true. Presumably, you want:

where Req_ecCode1 like '%YC%' or
      Req_ecCode2 like '%YC%' or
      Req_ecCode3 like '%YC%'

Or, if you don't want to repeat the pattern, use a delimiter:

where Req_ecCode1 + '|' + Req_ecCode2 + '|' + Req_ecCode3 like '%YC%'

Upvotes: 3

Ajan Balakumaran
Ajan Balakumaran

Reputation: 1649

You can use a case statement to identify that and use OR in where condition to check for YC, 

 SELECT *,
    CASE WHEN Req_ecCode1 like '%YC%' then 1 else 0 end as Req_ecCode1,
    CASE WHEN  Req_ecCode2 like '%YC%' then 1 else 0 end as Req_ecCode2,
    CASE WHEN  Req_ecCode3 like '%YC%' then 1 else 0 end as Req_ecCode3,
    FROM SC_Main
    WHERE Req_ecCode1 like '%YC%' OR Req_ecCode2 like '%YC%' OR Req_ecCode3 like '%YC%'

Upvotes: 4

Related Questions