select freqDescDetailCode from table1 freqDescDetailCode字段是nvarchar 返回的数据如下：
0800
0800-1600
0800-1600
0800-1200-1600
0800-1200-1600
以上数据有的不包含- 有的包含1个- 有2包含2个 或者N个
现在希望通过sqlserver技术手段返回
8:00
8:00-16:00
8:00-16:00
8:00-12:00-16:00
8:00-12:00-16:00
------解决思路----------------------
create table #t1(id int identity(1,1),val Nvarchar(200))
insert into #t1
select '0800' union all
select '0800-1600' union all
select '0800-1600' union all
select '0800-1200-1600' union all
select '0800-1200-1600'
;with sel1 as(
select a.id,substring(a.val,b.number,charindex('-',a.val+'-',b.number)-b.number) as newval
from #t1 a
join master..spt_values b on b.type='p' and b.number>0
and substring('-'+a.val,number,1)='-'
),
sel2 as
(select id,left(newval,2)+':'+right(newval,2) as val from sel1)
--select * from sel2
select val=stuff((select '-'+val from sel2 where id=t.id
for xml path('')),1,1,'')
from sel2 t
group by id
------解决思路----------------------
N个'-' 1楼的方法不合适吧 ，可以创建函数。
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
CREATE function [dbo].[testfun] (@date nvarchar(max))
returns nvarchar(max)
as
begin
declare @setdate nvarchar(max)
declare @redate nvarchar(max)
declare @enddate nvarchar(max)
set @date=@date+'-'
set @enddate=''
while @date<>''
begin
set @redate= stuff( @date,3,0,':')
set @setdate=substring(@redate,1, CHARINDEX('-',@redate))
set @enddate=@enddate+@setdate
set @date=stuff(@date,1,5,'' )
end
set @enddate=left(@enddate,len(@enddate)-1)
return @enddate
end
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
测试
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
use test
---->测试数据
IF OBJECT_ID('TEMPDB.DBO.#TEMP')IS NOT NULL
DROP TABLE #TEMP
------------------------------------------------------------------------------------------------------------
CREATE TABLE #TEMP
(DATE NVARCHAR (200)
)
INSERT INTO #TEMP
SELECT '0800'UNION ALL
SELECT '0800-1600'UNION ALL
SELECT '0800-1600'UNION ALL
SELECT '0800-1200-1600'UNION ALL
SELECT '0800-1200-1600-1500'UNION all
select '0800-1200-1600-1500-1600-1800'
go
select dbo.testfun(date) from #temp
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
结果
(6 行受影响)
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
08:00
08:00-16:00
08:00-16:00
08:00-12:00-16:00
08:00-12:00-16:00-15:00
08:00-12:00-16:00-15:00-16:00-18:00
(6 行受影响)
------解决思路----------------------
N个"-"时这样用
use Tempdb
go
--> -->
if not object_id(N'table1') is null
drop table table1
Go
Create table table1([freqDescDetailCode] nvarchar(50))
Insert table1
select N'0800' union all
select N'0800-1600' union all
select N'0800-1600' union all
select N'0800-1200-1600' union all
select N'0800-1200-1600'
go
select
a.Col1 as OldCol,stuff(b.[freqDescDetailCode],1,1,'') as [freqDescDetailCode]
from
(select [freqDescDetailCode] as Col1,cast('<Rows><Row>'+replace([freqDescDetailCode],'-','</Row><Row>')+'</Row></Rows>' as xml) as [freqDescDetailCode] from table1) as a
cross apply(
select '-'+stuff(t.c.value('(./text())[1]','varchar(1000)'),3,0,':') from a.[freqDescDetailCode].nodes('Rows/Row')t(c) for xml path('')
) as b([freqDescDetailCode])
/*
OldCol freqDescDetailCode
0800 08:00
0800-1600 08:00-16:00
0800-1600 08:00-16:00
0800-1200-1600 08:00-12:00-16:00
0800-1200-1600 08:00-12:00-16:00
/*
------解决思路----------------------
写个补冒号的函数你试试
select dbo.gettimegroup(freqdescdetailcode) from table1
create function gettimegroup (@strtime varchar(200))
returns varchar(200)
as
begin
declare @return varchar(200)
set @return='';
if len(@strtime)>=4
begin
declare @ct int
declare @i int
set @ct=0;
set @i=1;
set @ct=len(@strtime)-len(replace(@strtime,'-',''));
while @i<=(@ct+1)
begin
set @strtime=left(@strtime,(2+(@i-1)*6))+':'+right(@strtime,len(@strtime)-(2+(@i-1)*6))