Otshepeng Ditshego Otshepeng Ditshego - 11 months ago 112
SQL Question

Extract Text(Actual Month name) from text

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|

Answer Source

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download