如何提取一例中的一部分字符重新生成一例？

Question

例子：
object_accession_id
2-phospho-D- glycerate hydro-lyase|2-phosphoglycerate dehydratase|OSE1
LOC_Os03g15950
ACPIIILOC_Os08g43580
OJ1282_H11.31-1LOC_Os02g43090
P0485B12.17|P0485B12.17 proteinLOC_Os01g60660生成的结果是：object_accession_id                                 gene_id
2-phospho-D- glycerate hydro-lyase|2-phosphoglycerate dehydratase|OSE1       null
LOC_Os03g15950                                                              LOC_Os03g15950
ACPIIILOC_Os08g43580
OJ1282_H11.31-1LOC_Os02g43090                                               LOC_Os02g43090
P0485B12.17|P0485B12.17 proteinLOC_Os01g60660                               LOC_Os01g60660规律就是将以LOC开头的字符提出来，一共14个字符，并生成新的一列。

Answer 1

例子： 
object_accession_id 
2-phospho-D- glycerate  
LOC_Os03g15950 
ACPIIILOC_Os08g43580 
OJ1282_H11.31-1LOC_Os02g43090 
P0485B12.17|P0485B12.17 proteinLOC_Os01g60660 生成的结果是： object_accession_id                                                           gene_id 
2-phospho-D- glycerate                                                            null 
LOC_Os03g15950                                                                 LOC_Os03g15950 
ACPIIILOC_Os08g43580 
OJ1282_H11.31-1LOC_Os02g43090                                                  LOC_Os02g43090 
P0485B12.17|P0485B12.17 proteinLOC_Os01g60660                                  LOC_Os01g60660 规律就是将以LOC开头的字符提出来，一共14个字符，并生成新的一列。有点长，发出来就乱了，缩短一下。

Answer 2

drop table tb123001
go
create table tb123001(object_accession_id varchar(400))
insert into tb123001
select '2-phospho-D- glycerate' union all  
select 'LOC_Os03g15950' union all
select 'ACPIIILOC_Os08g43580' union all
select 'OJ1282_H11.31-1LOC_Os02g43090' union all
select 'P0485B12.17|P0485B12.17 proteinLOC_Os01g60660' 
go
alter table tb123001 add gene_id varchar(100)
go
declare @table table(a varchar(400),b varchar(100))
insert into @table
select object_accession_id,
       case when charindex('LOC',object_accession_id)>0 then 
      substring(object_accession_id,charindex('LOC',object_accession_id),len(object_accession_id))
      else null end
from tb123001
 
truncate table tb123001
insert into tb123001 select * from @table
select * from tb123001/*
2-phospho-D- glycerate NULL
LOC_Os03g15950 LOC_Os03g15950
ACPIIILOC_Os08g43580 LOC_Os08g43580
OJ1282_H11.31-1LOC_Os02g43090 LOC_Os02g43090
P0485B12.17|P0485B12.17 proteinLOC_Os01g60660 LOC_Os01g60660
*/