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 |
CodePudding user response:
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