Otshepeng Ditshego Otshepeng Ditshego - 3 years ago 259
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');

    month = m.month,
    [OPS/SLA]= substring(t.[Ops/SLA Month],LEN(m.month)+1,8000)  
from tbl t cross join 
where t.[Ops/SLA Month] like m.month +'%'

working demo

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