Address par. - Floor #

Question

I have some address data that needs parsing. Most of the address information is suitable for me, except for gender data.

For gender data, I get different ways, I can get the following formats:

"123 Main Street Floor 2" OR "123 Main Street 2nd Floor"

If I use the case ... when the instruction is to analyze my data, how can I determine when to get the number after or the number earlier? Ultimately, I want, for example, "Floor 2", for example, 1 and "2nd floor", for example 2.

This is the sql that I am currently using.

CASE WHEN (addr LIKE 'floor%' and addr LIKE '%[0-9]%' ) THEN SUBSTRING(addr, CHARINDEX('Floor ', addr), 
LEN(addr))

Answer 1

, PatIndex()

Declare @YourTable table (addr varchar(50))
Insert into @YourTable values
 ('123 Main Street Floor 2')
,('123 Main Street 2nd Floor')
,('123 Main Street')

select *
       ,FloorNr = NullIf(ltrim(rtrim(substring(addr,patindex('%Floor [1-9]%',addr)+patindex('% [1-9]% Floor',addr),25))),addr)
From @YourTable 

addr                        FloorNr
123 Main Street Floor 2     Floor 2
123 Main Street 2nd Floor   2nd Floor
123 Main Street             NULL

Answer 2

, , ...

declare @table table (addresses varchar(4000))

insert into @table
values
('123 Main Street Floor 2'),
('123 Main Street 2nd Floor')

select
    case 
        when right(addresses,5) = 'Floor' then right(addresses,CHARINDEX(' ',reverse(addresses),7)) 
        else substring(addresses,charindex('Floor',addresses,1),99) end
from
    @table