求一SQL函数

Question

参数个数2
1：字符串类型,例：'08:00-09:00,10:00-11:00,11:00-12:30'
2: 长度为1字符串 ，例：‘,’
返回类型 Table（beginTime varchar(5),endTime varchar(5)）
例：
beginTime endTime
08:00     09:00
10:00     11:00
11:00     12:30

Answer 1

基本思路:
replace(@s,',','union select')

Answer 2

DECLARE @str varchar(500);
SET @str = '08:00-09:00,10:00-11:00,11:00-12:30';SELECT LEFT(s,CHARINDEX('-',s)-1),RIGHT(s,CHARINDEX('-',REVERSE(s))-1)
FROM (
    SELECT SUBSTRING(@str,number,CHARINDEX(',',@str+',',number)-number) AS s
    FROM master.dbo.spt_values 
    WHERE type = 'p' AND SUBSTRING(','+@str,number,1)=','
) AS T/*
08:00 09:00
10:00 11:00
11:00 12:30
*/

Answer 3

IF object_id('fn_gettime') IS NOT NULL
DROP FUNCTION fn_gettime
GO
CREATE FUNCTION fn_gettime (@s VARCHAR(50))
RETURNS @r TABLE(beginTime varchar(5),endTime varchar(5))
AS
BEGIN
SET @s=RTRIM(LTRIM(@s))
IF RIGHT(@s,1)<>',' 
SET @s=@s+','
WHILE LEN(@s)>0
BEGIN
INSERT @r 
SELECT LEFT(LEFT(@s,CHARINDEX(',',@s)-1),5),
   RIGHT(LEFT(@s,CHARINDEX(',',@s)-1),5)
SET @s=STUFF(@s,1,CHARINDEX(',',@s),'')
END
RETURN
END
GO
DECLARE @s VARCHAR(50)
SET @s='08:00-09:00,10:00-11:00,11:00-12:30' 
SELECT * FROM dbo.fn_gettime(@s)
/*
beginTime endTime
--------- -------
08:00     09:00
10:00     11:00
11:00     12:30(3 行受影响)
*/

Answer 4

 SELECT SUBSTRING(@str,number,CHARINDEX(',',@str+',',number)-number) AS s
中number是什么意思！ 

Answer 5

CREATE FUNCTION dbo.SplitStr(@str varchar(1000))
    RETURNS TABLE(beginTime varchar(5),endTime varchar(5))
AS
    RETURN(
        SELECT LEFT(s,CHARINDEX('-',s)-1) AS beginTime,
            RIGHT(s,CHARINDEX('-',REVERSE(s))-1) AS endTime
        FROM (
           SELECT SUBSTRING(@str,number,CHARINDEX(',',@str+',',number)-number) AS s
           FROM master.dbo.spt_values 
           WHERE type = 'p' AND SUBSTRING(','+@str,number,1)=','
) AS T    );
GO
SELECT * FROM dbo.SplitStr('08:00-09:00,10:00-11:00,11:00-12:30')

Answer 6

CREATE FUNCTION dbo.SplitStr(@str varchar(1000))
    RETURNS TABLE
AS
    RETURN(
        SELECT LEFT(s,CHARINDEX('-',s)-1) AS beginTime,
            RIGHT(s,CHARINDEX('-',REVERSE(s))-1) AS endTime
        FROM (
           SELECT SUBSTRING(@str,number,CHARINDEX(',',@str+',',number)-number) AS s
           FROM master.dbo.spt_values 
           WHERE type = 'p' AND SUBSTRING(','+@str,number,1)=','
        ) AS T
    );
GO
SELECT * FROM dbo.SplitStr('08:00-09:00,10:00-11:00,11:00-12:30')GO
DROP FUNCTION dbo.SplitStr/*
beginTime endTime
08:00 09:00
10:00 11:00
11:00 12:30
*/

Answer 7

3楼和5楼 谢谢啊
我没想到用 LEFT和RiGHT 

Answer 8

7楼的将returns table后面的列的定义去掉就行了.

Answer 9

 create table #t1 (col varchar(100))
 declare @string1 varchar(100) 
set @string1='08:00-09:00,10:00-11:00,11:00-12:30'
 set @string1='select '''+replace(@string1,',',''' union all select ''')+''''
 
 
insert #t1
exec(@string1)
 
select beginTime=substring(col,1,charindex('-',col)-1), endTime=stuff(col,1,charindex('-',col),'')  from #t1 /*
beginTime                                                                                            endTime
---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
08:00                                                                                                09:00
10:00                                                                                                11:00
11:00                                                                                                12:30(3 行受影响)
*/drop table #t1

Answer 10

9楼这句没看懂啊SELECT SUBSTRING(@str,number,CHARINDEX(',',@str+',',number)-number) AS s
           FROM master.dbo.spt_values 
           WHERE type = 'p' AND SUBSTRING(','+@str,number,1)=','加个注释啊

Answer 11

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: 杜小七
-- Create date: 2009-09-17
-- Description: 自定义分隔符 函数
-- =============================================
create FUNCTION MySplit
(
@parm varchar(256),  --要分割的字符串
@Separator1 varchar(2),--分隔符1 ','
@Separator2 varchar(2)--分隔符2 '-'
)
RETURNS @tt TABLE(beginTime varchar(16),endTime varchar(16)) 
AS
begin
declare @temp varchar(32)  --临时存放字符串
declare @begintime varchar(16)--存放开始时间
declare @endtime varchar(16)--存放结束时间
while(charindex(',',@parm)<>0)
begin
set @temp=substring(@parm,0,charindex(@Separator1,@parm));
set @begintime=substring(@temp,0,charindex(@Separator2,@temp))
set @endtime=substring(@temp,charindex(@Separator2,@temp)+1,len(@temp))
insert into @tt values(@begintime,@endtime)
set @parm=stuff(@parm,1,charindex(@Separator1,@parm),'')
end
if(charindex(@Separator2,@parm)<>0)
begin
set @begintime=substring(@parm,0,charindex(@Separator2,@parm))
set @endtime=substring(@parm,charindex(@Separator2,@parm)+1,len(@parm))
insert into @tt values(@begintime,@endtime)
endRETURN 
endGO