MSSQL 字符串查找和截取疑问
本帖最后由 mic0601 于 2013-12-23 14:32:25 编辑 我有一个列，现在需要截取数据
比如这4个字符串，怎么截取'DE_F_25-34',
DE代表国家，F是性别，25-34是年龄段，所以DE也有可能是DEE三个字符
GC_MP_RH_DE_F_25-34
GC_MP_RHSDH_DE_F_25-34
GC_MP_RH_DE_F_25-34njsdh
---DE_F_25-34
[解决办法]
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-12-23 14:34:30
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
--Dec 28 2012 20:23:12
--Copyright (c) Microsoft Corporation
--Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([col] varchar(50))
insert [huang]
select 'GC_MP_RH_DE_F_25-34' union all
select 'GC_MP_RHSDH_DE_F_25-34' union all
select 'GC_MP_RH_DEEEEE_F_25-34njsdh'
--------------开始查询--------------------------
select substring(col,charindex('H_',col,1)+2,PATINDEX('%-%',col)-charindex('H_',col,1)-2)
from [huang]
----------------结果----------------------------
/*
--------------------------------------------------
DE_F_25
DE_F_25
DEEEEE_F_25
*/
create table #TB1 ([col] varchar(24))
insert #TB1
select 'GC_MP_RH_DE_F_25-34' union all
select 'GC_MP_RHSDH_DE_F_25-34' union all
select 'GC_MP_RH_DE_F_25-34njsdh' union all
select 'GC_MP_RH_DDDE_F_25-34' union all
select 'GC_MP_RHSDH_DEE_F_25-34'
--------------开始查询--------------------------
select top 0 * into #TB2 from #TB1
declare @i int
set @i=1
while(@i<=3)
begin
truncate table #TB2
insert into #TB2
select SUBSTRING(COL,CHARINDEX('_',col)+1,LEN(col)) from #TB1
truncate table #TB1
insert into #TB1
select * from #TB2
select @i=@i+1
end
select SUBSTRING(col,1,CHARINDEX('-',col)+2) From #TB2
