当前位置:数据库 > SQL语言> 正文

sql stuff 函数

时间:2014-11-21类别:数据库

sql stuff 函数

sql stuff 函数

一、作用

删除指定长度的字符,并在指定的起点处插入另一组字符。

二、语法

STUFF ( character_expression , start , length ,character_expression )

参数


character_expression
一个字符数据表达式。character_expression 可以是常量、变量,也可以是字符列或二进制数据列。

start
一个整数值,指定删除和插入的开始位置。如果 start 或 length 为负,则返回空字符串。如果 start 比第一个 character_expression 长,则返回空字符串。start 可以是 bigint 类型。

length
一个整数,指定要删除的字符数。如果 length 比第一个 character_expression 长,则最多删除到最后一个 character_expression 中的最后一个字符。length 可以是 bigint 类型。

返回类型
如果 character_expression 是受支持的字符数据类型,则返回字符数据。如果 character_expression 是一个受支持的 binary 数据类型,则返回二进制数据。


三、备注


1、如果开始位置或长度值是负数,或者如果开始位置大于第一个字符串的长度,将返回空字符串。如果要删除的长度大于第一个字符串的长度,将删除到第一个字符串中的第一个字符。


2、如果结果值大于返回类型支持的最大值,则产生错误。

四、sql stuff函数

实例1

  •  
  • 
    select STUFF('abcdefg',1,0,'1234')       --结果为'1234abcdefg'
    select STUFF('abcdefg',1,1,'1234')       --结果为'1234bcdefg'
    select STUFF('abcdefg',2,1,'1234')       --结果为'a1234cdefg'
    select STUFF('abcdefg',2,2,'1234')       --结果为'a1234defg'
    
    		
  • 实例2、SQL 将列转成字符串并用逗号分隔

    SELECT STUFF((SELECT ',' + FieldName FROM TableName FOR XML PATH('')),1,1,'') AS T
     

    其中的逗号可以换成其它字符
    
    
    
    转换完成后的结果如下:
    
    
    111,222,333,444,555,666
    
    

    实例3

    比如表中有两列数据

    ep_classes  ep_name

    AAA         企业1

    AAA         企业2

    AAA         企业3

    BBB         企业4

    BBB         企业5

    我想把这个表变成如下格式

    ep_classes      ep_name

    AAA             企业1,企业2,企业3

    BBB             企业4,企业5

    SQL实现代码

  •  
  • SQL 代码   复制
  • 
    select ep_classes, ep_name = (stuff((select ',' + ep_name from ep_detail where ep_classes = 
        a.ep_classes for xml path('')),1,1,'')) from ep_detail a group by ep_classes
    
    		
  • 实例4

  •  
  • SQL 代码   复制
  • 
    --提取数字
    IF OBJECT_ID('DBO.GET_NUMBER') IS NOT NULL
    DROP FUNCTION DBO.GET_NUMBER
    GO
    CREATE FUNCTION DBO.GET_NUMBER(@S VARCHAR(100))
    RETURNS VARCHAR(100)
    AS
    BEGIN
    WHILE PATINDEX('%[^0-9]%',@S) > 0
    BEGIN
    set @s=stuff(@s,patindex('%[^0-9]%',@s),1,'')
    END
    RETURN @S
    END
    GO
    --测试
    PRINT DBO.GET_NUMBER('呵呵ABC-12,3ABC')
    GO
    --123
    --------------------------------------------------------------------
    --提取英文
    IF OBJECT_ID('DBO.GET_STR') IS NOT NULL
    DROP FUNCTION DBO.GET_STR
    GO
    CREATE FUNCTION DBO.GET_STR(@S VARCHAR(100))
    RETURNS VARCHAR(100)
    AS
    BEGIN
    WHILE PATINDEX('%[^a-z]%',@S) > 0
    BEGIN
    set @s=stuff(@s,patindex('%[^a-z]%',@s),1,'')
    END
    RETURN @S
    END
    GO
    --测试
    PRINT DBO.GET_STR('呵呵ABC123ABC')
    GO
    --------------------------------------------------------------------
    --提取中文
    IF OBJECT_ID('DBO.CHINA_STR') IS NOT NULL
    DROP FUNCTION DBO.CHINA_STR
    GO
    CREATE FUNCTION DBO.CHINA_STR(@S NVARCHAR(100))
    RETURNS VARCHAR(100)
    AS
    BEGIN
    WHILE PATINDEX('%[^吖-座]%',@S) > 0
    SET @S = STUFF(@S,PATINDEX('%[^吖-座]%',@S),1,N'')
    RETURN @S
    END
    GO
    PRINT DBO.CHINA_STR('呵呵ABC123ABC')
    GO
    --------------------------------------------------------------------
    --过滤重复字符
    IF OBJECT_ID('DBO.DISTINCT_STR') IS NOT NULL
    DROP FUNCTION DBO.DISTINCT_STR
    GO
    CREATE FUNCTION DBO.DISTINCT_STR(@S NVARCHAR(100),@SPLIT VARCHAR(50))
    RETURNS VARCHAR(100)
    AS
    BEGIN
    IF @S IS NULL RETURN(NULL)
    DECLARE @NEW VARCHAR(50),@INDEX INT,@TEMP VARCHAR(50)
    IF LEFT(@S,1)<>@SPLIT
    SET @S = @SPLIT+@S
    IF RIGHT(@S,1)<>@SPLIT
    SET @S = @S+@SPLIT
    WHILE CHARINDEX(@SPLIT,@S)>0 AND LEN(@S)<>1
    BEGIN
    SET @INDEX = CHARINDEX(@SPLIT,@S)
    SET @TEMP = LEFT(@S,CHARINDEX(@SPLIT,@S,@INDEX+LEN(@SPLIT)))
    IF @NEW IS NULL 
    SET @NEW = ISNULL(@NEW,'')+@TEMP
    ELSE
    SET @NEW = ISNULL(@NEW,'')+REPLACE(@TEMP,@SPLIT,'')+@SPLIT
    WHILE CHARINDEX(@TEMP,@S)>0
    BEGIN
    SET @S=STUFF(@S,CHARINDEX(@TEMP,@S)+LEN(@SPLIT),CHARINDEX(@SPLIT,@S,CHARINDEX(@TEMP,@S)+LEN(@SPLIT))-CHARINDEX(@TEMP,@S),'')
    END
    END
    RETURN RIGHT(LEFT(@NEW,LEN(@NEW)-1),LEN(LEFT(@NEW,LEN(@NEW)-1))-1)
    END
    GO
    PRINT DBO.DISTINCT_STR('A,A,B,C,C,B,C,',',')
    --A,B,C
    GO
    --------------------------------------------------------------------
    --过滤重复字符2
    IF OBJECT_ID('DBO.DISTINCT_STR2') IS NOT NULL
    DROP FUNCTION DBO.DISTINCT_STR2
    GO
    CREATE FUNCTION DBO.DISTINCT_STR2(@S varchar(8000))
    RETURNS VARCHAR(100)
    AS
    BEGIN
    IF @S IS NULL RETURN(NULL)
    DECLARE @NEW VARCHAR(50),@INDEX INT,@TEMP VARCHAR(50)
    WHILE LEN(@S)>0
    BEGIN
    SET @NEW=ISNULL(@NEW,'')+LEFT(@S,1)
    SET @S=REPLACE(@S,LEFT(@S,1),'')
    END
    RETURN @NEW
    END
    GO
    SELECT DBO.DISTINCT_STR2('AABCCD')
    --ABCD
    GO
    
    --------------------------------------------------------------------
    IF OBJECT_ID('DBO.SPLIT_STR') IS NOT NULL
    DROP FUNCTION DBO.SPLIT_STR
    GO
    CREATE FUNCTION DBO.SPLIT_STR(
    @S varchar(8000),      --包含多个数据项的字符串
    @INDEX int,             --要获取的数据项的位置
    @SPLIT varchar(10)     --数据分隔符
    )
    RETURNS VARCHAR(100)
    AS
    BEGIN
    IF @S IS NULL RETURN(NULL)
    DECLARE @SPLITLEN int
    SELECT @SPLITLEN=LEN(@SPLIT+'A')-2
    WHILE @INDEX>1 AND CHARINDEX(@SPLIT,@S+@SPLIT)>0
    SELECT @INDEX=@INDEX-1,@S=STUFF(@S,1,CHARINDEX(@SPLIT,@S+@SPLIT)+@SPLITLEN,'')
    RETURN(ISNULL(LEFT(@S,CHARINDEX(@SPLIT,@S+@SPLIT)-1),''))
    END
    GO
    PRINT DBO.SPLIT_STR('AA|BB|CC',2,'|')
    --
    GO
    
    		
  • 标签:
    上一篇下一篇

    猜您喜欢

    热门推荐