'How do I define a variable based on a Case statement or If statement in the middle of SELECT statement?
I am defining the prefix of an address and then trying to remove it from the address. How can I define a variable using an if statement or case statement in the middle of a select statement? Is this possible?
DECLARE @address VARCHAR(100)
DECLARE @number VARCHAR(10)
DECLARE @prefix VARCHAR(10)
SELECT
@address = '7357 N Great Meadow St.',
@number = LEFT(@address, CHARINDEX(' ', @address)),
@address = REPLACE(@address, @number, ''),
@prefix = LEFT(@address, CHARINDEX(' ', (@address))),
CASE
WHEN(@prefix = 'N ')
OR (@prefix = 'S ')
OR (@prefix = 'E ')
OR (@prefix = 'W ')
THEN @address = REPLACE(@address, @prefix, '')
ELSE @address = @address
END
FROM myTable
WHERE 1 = 1
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
Solution | Source |
---|