SQL Server : convert column by different condition in select statement

Question

I have a table that contains a string address. I want to select the column text in a specific format. However, the column contains a different format and it requires different rules. For example, if the column contains words like 'SHOP', it will select the wording start with 'SHOP'. If the column contains words like 'BOX', it will select the wording after 'BOX'.

Table A

| columna | address          |
+---------+------------------+
| a1234   | ddsa SHOP LG123  |
| 4322    | SADA BOX 12-42   |
| 4632    | 123123 ADV  2313 |

I want something like this: select by the different conditions in different rules in the same column.

SELECT 
    ta.columna,
    if CHARINDEX('SHOP',ta.address) > 0
        RIGHT(ta.address, len(ta.address) - charindex('SHOP', ta.address)+1) AS unit_addr,
    if CHARINDEX('BOX',ta.address) > 0
        RIGHT(ta.address, len(ta.address) - charindex('BOX', ta.address)-8) AS unit_addr,
    if CHARINDEX('ADV',ta.address) > 0
        RIGHT(ta.address, charindex('ADV', ta.address)-3) AS unit_addr
FROM 
    tableA ta

So the output should be this:

| columna | address        |
+---------+----------------+
| a1234   | SHOP LG123     |
| 4322    | 12-42          |
| 4632    | 2313           |

Answer 1

You need to use a Case expression. Assuming the logic above does what you need, this sort of thing should provide the result in the structure you want...

select ta.columna,
       case 
           when CHARINDEX('SHOP',ta.address) > 0 then
               RIGHT(ta.address, len(ta.address) - charindex('SHOP', ta.address)+1)
           when CHARINDEX('LIGHTBOX',ta.address) > 0 then
               RIGHT(ta.address, len(ta.address) - charindex('LIGHTBOX', ta.address)-8)
           when CHARINDEX('ADV',ta.address) > 0 then
               RIGHT(ta.address, charindex('ADV', ta.address)-3)
       end as address
from tablea ta