Extract Text(Actual Month name) from text

Question

I have a column that has a month name in it as well as other text after the name of a specific month. What i want to do is extract the Months from that column and create a new column named Month.

|Ops/SLA Month      |

|APRIL SLA Reporting|

|APRIL OPS Reporting|

The column goes on and on with the months from april to march which would mean i cant use a substring since the months have different character lengths. Is there a way I can write a case statement or a query that will split this column into two columns? The intended results are as follows:

|Months |OPS/SLA      |

|April  |SLA Reporting|

|April  |OPS Reporting|

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

You can try a query like below

create table tbl ([Ops/SLA Month] varchar(max));
insert into tbl values
('APRIL SLA Reporting')
,('APRIL OPS Reporting');

select 
    month = m.month,
    [OPS/SLA]= substring(t.[Ops/SLA Month],LEN(m.month)+1,8000)  
from tbl t cross join 
    (values
         ('January'),('February'),('March'),
         ('April'),('may'),('june'),
         ('july'),('August'),('September'),
         ('October'),('November'),('December'))m(month)
where t.[Ops/SLA Month] like m.month +'%'

working demo

Extract Text(Actual Month name) from text

Sign up