I have some address data that needs parsing. Most of the addresses info is coming in a way that works for me except for floor data.
For floor data I get coming in different ways, I might get the following formats:
"123 Main Street Floor 2" OR "123 Main Street 2nd Floor"
If I'm using a case...when statement to parse my data, how do I get it to determine when to get the number after or the number before? Ultimately I want "Floor 2" for example 1 and "2nd Floor" for example 2.
This is the sql I am currently using.
CASE WHEN (addr LIKE 'floor%' and addr LIKE '%[0-9]%' ) THEN SUBSTRING(addr, CHARINDEX('Floor ', addr),
LEN(addr))