----------------------------------------------------------------
-- Author :DBA_HuangZJ(發糞塗牆)
-- Date :2014-09-02 17:22:59
-- Version:
-- Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
-- May 14 2014 18:34:29
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([col] varchar(12))
insert [huang]
select 's1-120112001' union all
select 's2-120202002'
--------------开始查询--------------------------
SELECT '20'+SUBSTRING(col,1,2)+'-'+SUBSTRING(col,3,2)+'-'+SUBSTRING(col,5,2)[NewCol]
FROM (
select SUBSTRING(col,PATINDEX('%-%',col)+1,LEN(col)-3-PATINDEX('%-%',col))[col]
from [huang])a
----------------结果----------------------------
/*
NewCol
----------------
2012-01-12
2012-02-02*/
-- Author :DBA_HuangZJ(發糞塗牆)
-- Date :2014-09-02 17:22:59
-- Version:
-- Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
-- May 14 2014 18:34:29
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([col] varchar(12))
insert [huang]
select 's1-120112001' union all
select 's2-120202002'
--------------开始查询--------------------------
SELECT '20'+SUBSTRING(col,1,2)+'-'+SUBSTRING(col,3,2)+'-'+SUBSTRING(col,5,2)[NewCol]
FROM (
select SUBSTRING(col,PATINDEX('%-%',col)+1,LEN(col)-3-PATINDEX('%-%',col))[col]
from [huang])a
----------------结果----------------------------
/*
NewCol
----------------
2012-01-12
2012-02-02*/
120112001
120202002
120202003(3 行受影响)
from tablename