现库中数据有点乱
有一个字段存有类似这几种值:
aaaa/bbb/2007-1-1
ccc ddd eeee 2007/1/2
f gg 2007/1/3怎么select后得到的是 aaaa bbb 批准日期:2007-1-1
ccc ddd eeee 批准日期:2007-1-2
f gg 批准日期:2007-1-3
有一个字段存有类似这几种值:
aaaa/bbb/2007-1-1
ccc ddd eeee 2007/1/2
f gg 2007/1/3怎么select后得到的是 aaaa bbb 批准日期:2007-1-1
ccc ddd eeee 批准日期:2007-1-2
f gg 批准日期:2007-1-3
ccc ddd eeee 批准日期:2007-1-2
f gg 批准日期:2007-1-3 结果显示成几个字段?一个字段?
--这样?
declare @t table(val varchar(100))
insert into @t select 'aaaa/bbb/2007-1-1'
insert into @t select 'ccc ddd eeee 2007/1/2'
insert into @t select 'f gg 2007/1/3'
select replace(left(val,len(val)-8),'/',' ')+'批准日期:'+right(val,8)
from @t
insert into @t values('ccc ddd eeee 2007/1/2')
insert into @t values('f gg 2007/1/3')
select
replace(left(code,charindex('2007',code)-1) ,'/',' ')+'批准日期:'+
replace(stuff(code,1,charindex('2007',code)-1,''),'/','-') as Code
from
@t t/*Code
---------------------------------
aaaa bbb 批准日期:2007-1-1
ccc ddd eeee 批准日期:2007-1-2
f gg 批准日期:2007-1-3
*/
有一个字段存有类似这几种值:
aaaa/bbb/2007-1-1
ccc ddd eeee 2007/1/2
f gg 2007/1/3 怎么select后得到的是 aaaa bbb 批准日期:2007-1-1
ccc ddd eeee 批准日期:2007-1-2
f gg 批准日期:2007-1-3 select replace(substring(col,1,len(col)-8) , '/' , ' ') + ' 批准日期:' + right(col , 8) from tb
create table #(str varchar(100))
insert into #
select 'aaaa/bbb/2007-1-1'
union all select 'ccc ddd eeee 2007/1/2'
union all select 'f gg 2007/1/3'怎么select后得到的是 aaaa bbb 批准日期:2007-1-1
ccc ddd eeee 批准日期:2007-1-2
f gg 批准日期:2007-1-3 */select replace(replace(left(str,charindex('20',str)-1),'-', ''),'/',' ')+' 批准日期:'+substring(str,charindex('20',str),8) from #
/*
------------------------------------------
aaaa bbb 批准日期:2007-1-1
ccc ddd eeee 批准日期:2007/1/2
f gg 批准日期:2007/1/3(所影响的行数为 3 行)
*/
————————————————————————————
类似数据如何处理?将"/dsdfds/fwew"从显示数据中剔除?
insert into tb values('aaaa/bbb/2007-1-1')
insert into tb values('ccc ddd eeee 2007/1/2')
insert into tb values('f gg 2007/1/3')
goselect col = replace(substring(col,1,len(col)-8) , '/' , ' ') + ' 批准日期:' + right(col , 8) from tbdrop table tb/*
col
------------------------------
aaaa bbb 批准日期:2007-1-1
ccc ddd eeee 批准日期:2007/1/2
f gg 批准日期:2007/1/3(所影响的行数为 3 行)
*/
select replace(replace(left(str,charindex('20',str)-1),'-', ''),'/',' ')+' 批准日期:'+substring(str,charindex('20',str),10) from #
————————————————————————————
类似数据如何处理?将"/dsdfds/fwew"从显示数据中剔除?----------这就麻烦了.
insert into @tb select 'aaaa/bbb/2007-1-1'
insert into @tb select 'ccc ddd eeee 2007/1/2'
insert into @tb select 'f gg 2007/1/3'select replace(case
when isdate(right(vc,8))=1 then left(vc,len(vc)-8)+'日期:'+convert(varchar(10),cast(right(vc,8) as datetime) ,120)
when isdate(right(vc,8))=1 then left(vc,len(vc)-9)+'日期:'+convert(varchar(10),cast(right(vc,9) as datetime) ,120)
when isdate(right(vc,8))=1 then left(vc,len(vc)-10)+'日期:'+convert(varchar(10),cast(right(vc,10) as datetime) ,120)
end ,'/',' ') as vc
from @tbaaaa bbb 日期:2007-01-01
ccc ddd eeee 日期:2007-01-02
f gg 日期:2007-01-03
--楼上的如果不是日期中出现2007,类似这样的数据“2007a bbb cc 2007/12/12”
--补充二楼的。考虑到日期可能出现“2007/12/02”
declare @t table(val varchar(100))
insert into @t select 'aaaa/bbb/2007-1-1'
insert into @t select 'ccc ddd eeee 2007/1/2'
insert into @t select 'f gg 2007/1/3'
select
val=case when isdate(right(val,8))=1 then replace(left(val,len(val)-8),'/',' ')+'批准日期:'+replace(right(val,8),'/','-')
when isdate(right(val,9))=1 then replace(left(val,len(val)-9),'/',' ')+'批准日期:'+replace(right(val,9),'/','-')
when isdate(right(val,10))=1 then replace(left(val,len(val)-10),'/',' ')+'批准日期:'+replace(right(val,10),'/','-') end
from @t
-----------------------------
要是数据格式不确定。估计是搞不了的
insert into tb values('aaaa/bbb/2007-1-1')
insert into tb values('ccc ddd eeee 2007/1/2')
insert into tb values('f gg 2007/1/3')
insert into tb values('aaaa/bbb/2007-1-1/dsdfds/fwew')
goselect col = replace(substring(col , 1 , len(col) - len(stuff(col,1,patindex('%[0-9]%',col)-1,''))),'/',' ') +
case when isdate(left(stuff(col,1,patindex('%[0-9]%',col)-1,''),8)) = 1 then left(stuff(col,1,patindex('%[0-9]%',col)-1,''),8)
when isdate(left(stuff(col,1,patindex('%[0-9]%',col)-1,''),9)) = 1 then left(stuff(col,1,patindex('%[0-9]%',col)-1,''),9)
when isdate(left(stuff(col,1,patindex('%[0-9]%',col)-1,''),10)) = 1 then left(stuff(col,1,patindex('%[0-9]%',col)-1,''),10)
end
from tbdrop table tb/*
col -----------------------
aaaa bbb 2007-1-1
ccc ddd eeee 2007/1/2
f gg 2007/1/3
aaaa bbb 2007-1-1
(所影响的行数为 4 行)
*/
insert into @t select 'aaaa/bbb/2007-1-1'
insert into @t select 'ccc ddd eeee 2007/1/2'
insert into @t select 'f gg 2007/1/3'
--在上面的基礎上修改.因為無法確定日期是 8,9,10 位
declare @datebeginpos int,
@i int,
@j int
select @i = 0,@datebeginpos = 1
while(@i<len(val))
begin
select @j = ISNUMERIC(SUBSTRING(val,@datebeginpos,1))
if (@j = 1)
BEGIN
SET @i = len(val) + 1
END
ELSE
BEGIN
SELECT @i = @i+1,@datebeginpos = @datepeginpos + 1
END
end
select replace(left(val,len(val)-@datebeginpos),'/',' ')+'批准日期:'+replace(right(val,@datebeginpos),'/','-')
from @t
insert into tb values('aaaa/bbb/2007-1-1')
insert into tb values('ccc ddd eeee 2007/1/2')
insert into tb values('f gg 2007/1/3')
insert into tb values('aaaa/bbb/2007-1-1/dsdfds/fwew')
goselect col = replace(substring(col , 1 , len(col) - len(stuff(col,1,patindex('%[0-9]%',col)-1,''))),'/',' ') +
case when isdate(left(stuff(col,1,patindex('%[0-9]%',col)-1,''),8)) = 1 then replace(left(stuff(col,1,patindex('%[0-9]%',col)-1,''),8),'/','-')
when isdate(left(stuff(col,1,patindex('%[0-9]%',col)-1,''),9)) = 1 then replace(left(stuff(col,1,patindex('%[0-9]%',col)-1,''),9),'/','-')
when isdate(left(stuff(col,1,patindex('%[0-9]%',col)-1,''),10)) = 1 then replace(left(stuff(col,1,patindex('%[0-9]%',col)-1,''),10),'/','-')
end
from tbdrop table tb/*
col
-----------------------
aaaa bbb 2007-1-1
ccc ddd eeee 2007/1/2
f gg 2007/1/3
aaaa bbb 2007-1-1
(所影响的行数为 4 行)
*/
insert into tb values('aaaa/bbb/2007-1-1')
insert into tb values('ccc ddd eeee 2007/1/2')
insert into tb values('f gg 2007/1/3')
insert into tb values('aaaa/bbb/2007-1-1/dsdfds/fwew')
goselect col = replace(substring(col , 1 , len(col) - len(stuff(col,1,patindex('%[0-9]%',col)-1,''))),'/',' ') +
case when isdate(left(stuff(col,1,patindex('%[0-9]%',col)-1,''),8)) = 1 then replace(left(stuff(col,1,patindex('%[0-9]%',col)-1,''),8),'/','-')
when isdate(left(stuff(col,1,patindex('%[0-9]%',col)-1,''),9)) = 1 then replace(left(stuff(col,1,patindex('%[0-9]%',col)-1,''),9),'/','-')
when isdate(left(stuff(col,1,patindex('%[0-9]%',col)-1,''),10)) = 1 then replace(left(stuff(col,1,patindex('%[0-9]%',col)-1,''),10),'/','-')
end
from tbdrop table tb/*
col
--------------
aaaa bbb 2007-1-1
ccc ddd eeee 2007-1-2
f gg 2007-1-3
aaaa bbb 2007-1-1
(所影响的行数为 4 行)
*/
create table tb(col varchar(30))
insert into tb values('aaaa/bbb/2007-1-1')
insert into tb values('ccc ddd eeee 2007/1/2')
insert into tb values('f gg 2007/1/3')
insert into tb values('aaaa/bbb/2007-1-1/dsdfds/fwew')
goselect col = replace(substring(col , 1 , len(col) - len(stuff(col,1,patindex('%[0-9]%',col)-1,''))),'/',' ') + '批准日期:'+
case when isdate(left(stuff(col,1,patindex('%[0-9]%',col)-1,''),8)) = 1 then replace(left(stuff(col,1,patindex('%[0-9]%',col)-1,''),8),'/','-')
when isdate(left(stuff(col,1,patindex('%[0-9]%',col)-1,''),9)) = 1 then replace(left(stuff(col,1,patindex('%[0-9]%',col)-1,''),9),'/','-')
when isdate(left(stuff(col,1,patindex('%[0-9]%',col)-1,''),10)) = 1 then replace(left(stuff(col,1,patindex('%[0-9]%',col)-1,''),10),'/','-')
end
from tbdrop table tb/*
col
-----------------------
aaaa bbb 批准日期:2007-1-1
ccc ddd eeee 批准日期:2007-1-2
f gg 批准日期:2007-1-3
aaaa bbb 批准日期:2007-1-1
(所影响的行数为 4 行)
*/
insert into tb values('aaaa/b3bb/2007-1-1/dsdfds/fwew')如果楼主的数据真的像那样完全无规律。是没有办法的。。
insert into @t select 'aaaa/bbb/2007-1-1'
insert into @t select 'ccc ddd eeee 2007/1/2'
insert into @t select 'f gg 2007/1/3'
--在上面的基礎上修改.因為無法確定日期是 8,9,10 位
declare @datebeginpos int,
@dateendpos int, --日期長度
@i int,
@j int,
@k int
select @i = 1,@dateendpos = 0,@k = 0
while(@i<=len(val))
begin
select @j = ISNUMERIC(SUBSTRING(val,@i,1))
if (@j = 1)
BEGIN
if(@k = 0)
BEGIN
select @datebeginpos = @dateendpos,@k = 1
END
set @dateendpos = @dateendpos + 1
END
SELECT @i = @i+1
end
select replace(left(val,len(val)-@datebeginpos),'/',' ')+'批准日期:'+replace(substring(val,@datebeginpos,@dateendpos),'/','-')
from @t
insert into @t select 'aaaa/bbb/2007-1-1'
insert into @t select 'ccc ddd eeee 2007/1/2'
insert into @t select 'f gg 2007/1/3'
--在上面的基礎上修改.因為無法確定日期是 8,9,10 位
declare @datebeginpos int, --日期起始位置
@dateendpos int, -- +2才是日期長度
@i int,
@j int,
@k int --@k = 0 判斷是不是日期起始位置
select @i = 1,@dateendpos = 0,@k = 0
while(@i <=len(val))
begin
select @j = ISNUMERIC(SUBSTRING(val,@i,1))
if (@j = 1)
BEGIN
if(@k = 0)
BEGIN
select @datebeginpos = @dateendpos,@k = 1
END
set @dateendpos = @dateendpos + 1
END
SELECT @i = @i+1
end
select replace(left(val,len(val)-@datebeginpos),'/',' ')+'批准日期:'+replace(substring(val,@datebeginpos,@dateendpos+2),'/','-')
from @t
insert into @t select 'aaaa/bbb/2007-1-1'
insert into @t select 'ccc ddd eeee 2007/1/2'
insert into @t select 'f gg 2007/1/3'
--在上面的基礎上修改.因為無法確定日期是 8,9,10 位
declare @datebeginpos int, --日期起始位置
@dateendpos int, -- +2才是日期長度
@i int,
@j int,
@k int --@k = 0 判斷是不是日期起始位置
select @i = 1,@dateendpos = 0,@k = 0
while(@i <=len(val))
begin
select @j = ISNUMERIC(SUBSTRING(val,@i,1))
if (@j = 1)
BEGIN
if(@k = 0)
BEGIN
select @datebeginpos = @i,@k = 1
END
set @dateendpos = @dateendpos + 1
END
SELECT @i = @i+1
end
select replace(left(val,len(val)-@datebeginpos),'/',' ')+'批准日期:'+replace(substring(val,@datebeginpos,@dateendpos+2),'/','-')
from @t
--在上面的基礎上修改.因為無法確定日期是 8,9,10 位
declare @datebeginpos int, --日期起始位置
@dateendpos int, -- +2才是日期長度
@i int,
@j int,
@k int, --@k = 0 判斷是不是日期起始位置
@len int,
@val varchar(8000)
select @val = 'gfsjjg/dfsd 2007/1/31 jhjgjgjh'
select @len = len(@val)
select @i = 1,@dateendpos = 0,@k = 0
while(@i <= @len)
begin
select @j = ISNUMERIC(SUBSTRING(@val,@i,1))
if (@j = 1)
BEGIN
if(@k = 0)
BEGIN
select @datebeginpos = @i,@k = 1
END
set @dateendpos = @dateendpos + 1
END
SELECT @i = @i+1
end
select replace(substring(@val,1,@datebeginpos-1),'/',' ')+'批准日期:'+replace(substring(@val,@datebeginpos,@dateendpos+2),'/','-')
gfsjjg dfsd 批准日期:2007-1-31(影響 1 個資料列)
insert into @a values('aaaa/bbb/2007-1-1')
insert into @a values('ccc ddd eeee 2007/1/2')
insert into @a values('f gg 2007/1/3')
insert into @a values('aaaa/bbb/2007-1-1/dsdfds/fwew')
insert into @a values('aaaa/bbbf/4/2007-12-1/dsdfds/fwew')SELECT a,replace(LEFT(a,pos-1),'-',' ')+'批准日期:'+substring(a,pos,leng) Value FROM
(
SELECT a,
CASE WHEN patindex('%[0-9][0-9][0-9][0-9]-[0-9]-[0-9]%',a)>0 THEN patindex('%[0-9][0-9][0-9][0-9]-[0-9]-[0-9]%',a)
WHEN patindex('%[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9]%',a)>0 THEN patindex('%[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9]%',a)
WHEN patindex('%[0-9][0-9][0-9][0-9]-[0-9]-[0-9]0-9]%',a)>0 THEN patindex('%[0-9][0-9][0-9][0-9]-[0-9]-[0-9][0-9]%',a)
WHEN patindex('%[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]%',a)>0 THEN patindex('%[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]%',a)
END pos,
CASE WHEN patindex('%[0-9][0-9][0-9][0-9]-[0-9]-[0-9]%',a)>0 THEN 8
WHEN patindex('%[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9]%',a)>0 THEN 9
WHEN patindex('%[0-9][0-9][0-9][0-9]-[0-9]-[0-9][0-9]%',a)>0 THEN 9
WHEN patindex('%[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]%',a)>0 THEN 10
END leng FROM (SELECT replace(a,'/','-') a FROM @a)aa
WHERE patindex('%[0-9][0-9][0-9][0-9]-[0-9]-[0-9]%',a)>0
OR patindex('%[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9]%',a)>0
OR patindex('%[0-9][0-9][0-9][0-9]-[0-9]-[0-9][0-9]%',a)>0
OR patindex('%[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]%',a)>0
)aa--result
/*a Value
-------------------------------------------------- --------------------------------------------------
aaaa-bbb-2007-1-1 aaaa bbb 批准日期:2007-1-1
ccc ddd eeee 2007-1-2 ccc ddd eeee 批准日期:2007-1-2
f gg 2007-1-3 f gg 批准日期:2007-1-3
aaaa-bbb-2007-1-1-dsdfds-fwew aaaa bbb 批准日期:2007-1-1
aaaa-bbbf-4-2007-12-1-dsdfds-fwew aaaa bbbf 4 批准日期:2007-12-1(所影响的行数为 5 行)
*/