Remove common substring from the strings in a column in SQL Server

 

Remove common substring from the strings:

 

       I have seen a very different request in one of the popular forum and I would like to write an article on this.

 

Problem:

 

    Am having a column with string values(as below), I need to remove the substring L1.1 / L1.2 / L2.1 / L3.1 etc..,

 

             'ADM L1.1 Mock Test'

             'ADM L2.1 Mock Test'

             '.Net L2.1' 

 

The above values needs to be changed to,

 

                'ADM Mock Test'

                'ADM Mock Test'
                '.Net ' 

 

We need to write a generic queries to achieve it,

 

drop table sample_table

create table sample_table(val varchar(100))

-- Inserting records in the table.
insert into sample_table select 'ADM L1.1 Mock Test' union all select 'ADM L2.1 Mock Test' union allselect 'ADM L2.2 Mock Test' union all select 'ADM L2.3 Mock Test' union all select '.Net L1.1'  union all select '.Net L2.1'  union all select '.Net L2.2'   union all select '.Net L2.3'  union all select '.Net L3.1'

select * from sample_table

select SUBSTRING(val,0,CHARINDEX('L',val)) + SUBSTRING(val,CHARINDEX('L',val)+4,LEN(val) )from sample_table

 

 

 

Cheers,

Venkatesan Prabu .J

http://venkattechnicalblog.blogspot.com/