FormatStr 格式化SQL

USE [DEV]
GO
/****** Object:  UserDefinedFunction [dbo].[FormatStr]    Script Date: 12/21/2011 17:32:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[FormatStr](@str varchar(1000))
returns varchar(1000)
as
begin
    --declare @str varchar(50)
    declare @lenStr int,@i int,@index int
    declare @tmpStr varchar(1000)
    declare @lastChar bit
    set @lastChar = 0
    set @i = 1
    --set @str = 'R11-103422,R11-103455,R11-103421,R11-103420'
    set @lenStr = len(@str);
    set @tmpStr =''
    set @index = 0;
    while (1=1)
    begin
        if CharIndex(',',@str,0) = 0
            begin
                Set @LastChar = 1;
                set @tmpStr = @tmpStr + '''' + @str + '''';
                set @tmpStr = '(' + @tmpStr + ')'
                return @tmpStr
            end       

        set @index = CharIndex(',',@str,0);
        --set @tmpStr = SubString(@str,1,@index);
        set @lenStr =len(@str);
        if @LastChar = 0
            begin
                set @tmpStr = @tmpStr + '''' + SubString(@str,1,@index-1) + ''','
                set @str = SubString(@str,@index+1,@lenStr-@index)
                set @i = @i +1
            end
        --else
            --begin
                --set @tmpStr = @tmpStr + '''' + SubString(@str,1,@index-1) + ''''
                --set @str = SubString(@str,@index+1,@lenStr-@index)
                --set @i = @i+1
                --break;
            --end
    end
    set @tmpStr = '(' + @tmpStr + ')'
    return @tmpStr
end

转载于:https://www.cnblogs.com/perock/archive/2011/12/21/2296107.html