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|

Check this answer: https://stackoverflow.com/questions/10921400/t-sql-substring-separating-first-and-last-name — Badea Mihai Florin, Oct 03 '17 at 07:18

score 3 · Accepted Answer · answered Oct 03 '17 at 07:16

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

Thanks a mil. This works perfectly but I will Modify it because i dont want to create a table — Otshepeng Ditshego, Oct 03 '17 at 07:25

Suvethan Nantha · Answer 2 · 2017-10-03T07:30:52.927

Try this

insert into <table_name1>
select 
    case when CHARINDEX(' ',[Ops/SLA Month])>0 
         then SUBSTRING([Ops/SLA Month],1,CHARINDEX(' ',[Ops/SLA Month])-1) 
         else [Ops/SLA Month] end Month, 
    CASE WHEN CHARINDEX(' ',[Ops/SLA Month])>0 
         THEN SUBSTRING([Ops/SLA Month],CHARINDEX(' ',[Ops/SLA Month])+1,len([Ops/SLA Month]))  
         ELSE NULL END as OPS/SLA
from <table_name2>;

Replace the table_name1,table_name2 with your fields.

score 0 · Answer 3 · answered Oct 03 '17 at 07:24

Assuming the first word (until first space) is always the month, the follwing should work:

SELECT
    CASE 
        WHEN CHARINDEX(' ', [OPS/SLA Month]) = 0 THEN NULL
        ELSE SUBSTRING([OPS/SLA Month], 1, CHARINDEX(' ', [OPS/SLA Month]) - 1)
        END AS [Months],
    CASE 
        WHEN CHARINDEX(' ', [OPS/SLA Month]) = 0 THEN [OPS/SLA Month]
        ELSE SUBSTRING([OPS/SLA Month], CHARINDEX(' ', [OPS/SLA Month]) + 1, 8000)
        END AS [OPS/SLA]
FROM myTable

Extract Text(Actual Month name) from text

3 Answers3