jason jason - 1 month ago 7
SQL Question

Parsing in TSQL

I have an SQL table which has a cell like this :


Outdoor pool, security, water tank


I need to convert that cell to this :



  • Column Outdoor pool => 1

  • Column indoor pool => 0

  • Column security => 1

  • Column watertank => 1




I have no idea how to do this. I don't want you to do this for me. But please give me some pointers on this.

Please tell me how I can parse that column and generate new columns and fill those columns. Thanks.

Answer

Use like:

select (case when col like '%outdoor pool%' then 1 else 0 end) as has_outdoorpool,
       (case when col like '%indoor pool%' then 1 else 0 end) as has_indoorpool,
       (case when col like '%security%' then 1 else 0 end) as has_security,
       (case when col like '%water tank%' then 1 else 0 end) as has_watertank