T-SQL의 기능과 같은 String.Format?

programing tip

T-SQL의 기능과 같은 String.Format?

itbloger 2020. 9. 22. 07:46

T-SQL의 기능과 같은 String.Format?

String.Format.NET 의 메서드 와 유사한 문자열 조작을 위해 T-SQL의 기본 제공 함수 / 확장 함수를 찾고 있습니다.

SQL Server 2012 이상을 사용하는 경우 FORMATMESSAGE. 예.

DECLARE @s NVARCHAR(50) = 'World';
DECLARE @d INT = 123;
SELECT FORMATMESSAGE('Hello %s, %d', @s, @d)
-- RETURNS 'Hello World, 123'

MSDN의 추가 예 : FORMATMESSAGE

SELECT FORMATMESSAGE('Signed int %i, %d %i, %d, %+i, %+d, %+i, %+d', 5, -5, 50, -50, -11, -11, 11, 11);
SELECT FORMATMESSAGE('Signed int with leading zero %020i', 5);
SELECT FORMATMESSAGE('Signed int with leading zero 0 %020i', -55);
SELECT FORMATMESSAGE('Unsigned int %u, %u', 50, -50);
SELECT FORMATMESSAGE('Unsigned octal %o, %o', 50, -50);
SELECT FORMATMESSAGE('Unsigned hexadecimal %x, %X, %X, %X, %x', 11, 11, -11, 50, -50);
SELECT FORMATMESSAGE('Unsigned octal with prefix: %#o, %#o', 50, -50);
SELECT FORMATMESSAGE('Unsigned hexadecimal with prefix: %#x, %#X, %#X, %X, %x', 11, 11, -11, 50, -50);
SELECT FORMATMESSAGE('Hello %s!', 'TEST');
SELECT FORMATMESSAGE('Hello %20s!', 'TEST');
SELECT FORMATMESSAGE('Hello %-20s!', 'TEST');
SELECT FORMATMESSAGE('Hello %20s!', 'TEST');

노트:

2012 년에 문서화되지 않음
2044 자로 제한됨
% 기호를 이스케이프하려면 두 배로해야합니다.
확장 이벤트에 오류를 기록하는 경우 호출 FORMATMESSAGE은 (무해한) 오류로 나타납니다.

xp_sprintf 살펴 보십시오 . 아래 예.

DECLARE @ret_string varchar (255)
EXEC xp_sprintf @ret_string OUTPUT, 
    'INSERT INTO %s VALUES (%s, %s)', 'table1', '1', '2'
PRINT @ret_string

결과는 다음과 같습니다.

INSERT INTO table1 VALUES (1, 2)

이 문자열의 최대 크기 (255 자 제한)에 문제가 있으므로 사용할 수 있는 대체 기능 이 있습니다.

create function dbo.fnSprintf (@s varchar(MAX), 
                @params varchar(MAX), @separator char(1) = ',')
returns varchar(MAX)
as
begin
declare @p varchar(MAX)
declare @paramlen int

set @params = @params + @separator
set @paramlen = len(@params)
while not @params = ''
begin
    set @p = left(@params+@separator, charindex(@separator, @params)-1)
    set @s = STUFF(@s, charindex('%s', @s), 2, @p)
    set @params = substring(@params, len(@p)+2, @paramlen)
end
return @s
end

위와 같은 결과를 얻으려면 다음과 같이 함수를 호출합니다.

print dbo.fnSprintf('INSERT INTO %s VALUES (%s, %s)', 'table1,1,2', default)

string.format 기능을 모방하기 위해 사용자 정의 함수를 만들었습니다. 사용할 수 있습니다.

stringformat-in-sql

방법이 있지만 한계가 있습니다. FORMATMESSAGE()기능을 사용할 수 있습니다 . printf()C 의 함수 와 유사한 형식을 사용하여 문자열을 형식화 할 수 있습니다 .

그러나 가장 큰 제한은 sys.messages 테이블의 메시지에서만 작동한다는 것입니다. 여기에 대한 기사가 있습니다. microsoft_library_ms186788

데이터베이스에서 문자열 / varchar의 형식을 지정하려는 경우가 있기 때문에이 작업을 수행하는 더 쉬운 방법이 없다는 것은 부끄러운 일입니다. 바라건대 당신은 표준 방식으로 문자열을 포맷하고 sys.messages테이블을 사용할 수 있기를 바랍니다 .

우연히도, RAISERROR()매우 낮은 심각도 의 함수를 사용할 수도 있습니다. raiseerror에 대한 문서는이 작업을 언급하기도하지만 결과 만 인쇄됩니다. 따라서 결과 값으로 아무것도 할 수 없습니다 (내가 이해하는 것에서).

행운을 빕니다!

원시 t-sql은 문자열 조작을 위해 CHARINDEX (), PATINDEX (), REPLACE () 및 SUBSTRING ()으로 제한됩니다. 그러나 SQL Server 2005 이상에서는 .Net에서 실행되는 사용자 정의 함수를 설정할 수 있습니다. 즉, string.format () UDF 설정이 너무 어렵지 않아야합니다.

한 가지 더.

이것은 보편적 인 해결책은 아니지만-적어도 나에게는 간단하고 작동합니다. :)

자리 표시 자 {0} 1 개의 경우 :

create function dbo.Format1
(
    @String  nvarchar(4000),
    @Param0  sql_variant
)
returns nvarchar(4000)
as
begin
    declare @Null nvarchar(4) = N'NULL';

    return replace(@String, N'{0}', cast(isnull(@Param0, @Null) as nvarchar(4000)));    
end

두 자리 표시 자 {0} 및 {1}의 경우 :

create function dbo.Format2
(
    @String  nvarchar(4000),
    @Param0  sql_variant,
    @Param1  sql_variant
)
returns nvarchar(4000)
as
begin
    declare @Null nvarchar(4) = N'NULL';

    set @String = replace(@String, N'{0}', cast(isnull(@Param0, @Null) as nvarchar(4000)));
       return     replace(@String, N'{1}', cast(isnull(@Param1, @Null) as nvarchar(4000))); 
end

세 개의 자리 표시 자 {0}, {1} 및 {2}의 경우 :

create function dbo.Format3
(
    @String  nvarchar(4000),
    @Param0  sql_variant,
    @Param1  sql_variant,
    @Param2  sql_variant
)
returns nvarchar(4000)
as
begin
    declare @Null nvarchar(4) = N'NULL';

    set @String = replace(@String, N'{0}', cast(isnull(@Param0, @Null) as nvarchar(4000)));
    set @String = replace(@String, N'{1}', cast(isnull(@Param1, @Null) as nvarchar(4000))); 
       return     replace(@String, N'{2}', cast(isnull(@Param2, @Null) as nvarchar(4000)));
end

등등...

이러한 접근 방식을 사용하면 SELECT 문과 nvarchar, number, bit 및 datetime 데이터 유형의 매개 변수와 함께 이러한 함수를 사용할 수 있습니다.

예를 들면 :

declare @Param0 nvarchar(10) = N'IPSUM' ,
        @Param1 int          = 1234567  ,
        @Param2 datetime2(0) = getdate();

select dbo.Format3(N'Lorem {0} dolor, {1} elit at {2}', @Param0, @Param1, @Param2);

끝 위치를 계산하는 동안 약간의 수정이 있다고 생각합니다.

다음은 올바른 기능입니다.

**>>**IF OBJECT_ID( N'[dbo].[FormatString]', 'FN' ) IS NOT NULL
DROP FUNCTION [dbo].[FormatString]
GO
/***************************************************
Object Name : FormatString
Purpose : Returns the formatted string.
Original Author : Karthik D V http://stringformat-in-sql.blogspot.com/
Sample Call:
SELECT dbo.FormatString ( N'Format {0} {1} {2} {0}', N'1,2,3' )
*******************************************/
CREATE FUNCTION [dbo].[FormatString](
    @Format NVARCHAR(4000) ,
    @Parameters NVARCHAR(4000)
)
RETURNS NVARCHAR(4000)
AS
BEGIN
    --DECLARE @Format NVARCHAR(4000), @Parameters NVARCHAR(4000) select @format='{0}{1}', @Parameters='hello,world'
    DECLARE @Message NVARCHAR(400), @Delimiter CHAR(1)
    DECLARE @ParamTable TABLE ( ID INT IDENTITY(0,1), Parameter VARCHAR(1000) )
    Declare @startPos int, @endPos int
    SELECT @Message = @Format, @Delimiter = ','**>>**

    --handle first parameter
     set @endPos=CHARINDEX(@Delimiter,@Parameters)
    if (@endPos=0 and @Parameters is not null) --there is only one parameter
        insert into @ParamTable (Parameter) values(@Parameters)
    else begin
        insert into @ParamTable (Parameter) select substring(@Parameters,0,@endPos)
    end

    while @endPos>0
    Begin
        --insert a row for each parameter in the 
        set @startPos = @endPos + LEN(@Delimiter)
        set @endPos = CHARINDEX(@Delimiter,@Parameters, @startPos)
        if (@endPos>0)
            insert into @ParamTable (Parameter) 
                select substring(@Parameters,@startPos,@endPos - @startPos)
            else
                insert into @ParamTable (Parameter) 
                select substring(@Parameters,@startPos,4000)            
    End

    UPDATE @ParamTable SET @Message = 
        REPLACE ( @Message, '{'+CONVERT(VARCHAR,ID) + '}', Parameter )
    RETURN @Message
END
Go
grant execute,references on dbo.formatString to public

여기 내 버전입니다. 더 많은 수의 매개 변수를 수용하도록 확장 할 수 있으며 유형에 따라 형식을 확장 할 수 있습니다. 현재 날짜 및 날짜 시간 유형 만 형식화됩니다.

예:

select dbo.FormatString('some string %s some int %s date %s','"abcd"',100,cast(getdate() as date),DEFAULT,DEFAULT)
select dbo.FormatString('some string %s some int %s date time %s','"abcd"',100,getdate(),DEFAULT,DEFAULT)

산출:

some string "abcd" some int 100 date 29-Apr-2017
some string "abcd" some int 100 date time 29-Apr-2017 19:40

기능 :

create function dbo.FormatValue(@param sql_variant)
returns nvarchar(100)
begin
/*
Tejasvi Hegde, 29-April-2017
Can extend formatting here.
*/
    declare @result nvarchar(100)

    if (SQL_VARIANT_PROPERTY(@param,'BaseType') in ('date'))
    begin
       select @result = REPLACE(CONVERT(CHAR(11), @param, 106), ' ', '-')
    end
    else  if (SQL_VARIANT_PROPERTY(@param,'BaseType') in ('datetime','datetime2'))
    begin
       select @result = REPLACE(CONVERT(CHAR(11), @param, 106), ' ', '-')+' '+CONVERT(VARCHAR(5),@param,108)
    end
    else
    begin
       select @result = cast(@param as nvarchar(100))
    end
    return @result

/*
BaseType:
bigint
binary
char
date
datetime
datetime2
datetimeoffset
decimal
float
int
money
nchar
numeric
nvarchar
real
smalldatetime
smallint
smallmoney
time
tinyint
uniqueidentifier
varbinary
varchar
*/   

end;


create function dbo.FormatString(
    @format nvarchar(4000)
    ,@param1 sql_variant = null
    ,@param2 sql_variant = null
    ,@param3 sql_variant = null
    ,@param4 sql_variant = null
    ,@param5 sql_variant = null
    )
returns nvarchar(4000)
begin
/*
Tejasvi Hegde, 29-April-2017

select dbo.FormatString('some string value %s some int %s date %s','"abcd"',100,cast(getdate() as date),DEFAULT,DEFAULT)
select dbo.FormatString('some string value %s some int %s date time %s','"abcd"',100,getdate(),DEFAULT,DEFAULT)
*/

    declare @result nvarchar(4000)

    select @param1 = dbo.formatValue(@param1)
    ,@param2 = dbo.formatValue(@param2)
    ,@param3 = dbo.formatValue(@param3)
    ,@param4 = dbo.formatValue(@param4)
    ,@param5 = dbo.formatValue(@param5)

    select @param2 = cast(@param2 as nvarchar)
    EXEC xp_sprintf @result OUTPUT,@format , @param1, @param2, @param3, @param4, @param5

    return @result

end;

여기에 내장 된

FORMATMESSAGE () 함수

sp_addmessage @msgnum=50001,@severity=1,@msgText='Hello %s you are #%d',@replace='replace'
SELECT FORMATMESSAGE(50001, 'Table1', 5)

sp_addmessage를 호출하면 메시지 템플릿이 시스템 테이블 master.dbo.sysmessages (SQLServer 2000에서 확인 됨)에 저장됩니다.

테이블에서 템플릿 문자열의 추가 및 제거를 직접 관리해야합니다. 정말 원하는 것이 결과 화면에 빠른 메시지를 출력하는 것이라면 어색합니다.

The solution provided by Kathik DV, looks interesting but doesn't work with SQL Server 2000, so i altered it a bit, and this version should work with all versions of SQL Server:

IF OBJECT_ID( N'[dbo].[FormatString]', 'FN' ) IS NOT NULL
    DROP FUNCTION [dbo].[FormatString]
GO
/***************************************************
Object Name : FormatString
Purpose : Returns the formatted string.
Original Author : Karthik D V http://stringformat-in-sql.blogspot.com/
Sample Call:
SELECT dbo.FormatString ( N'Format {0} {1} {2} {0}', N'1,2,3' )
*******************************************/
CREATE FUNCTION [dbo].[FormatString](
@Format NVARCHAR(4000) ,
@Parameters NVARCHAR(4000)
)
RETURNS NVARCHAR(4000)
AS
BEGIN
    --DECLARE @Format NVARCHAR(4000), @Parameters NVARCHAR(4000) select @format='{0}{1}', @Parameters='hello,world'
    DECLARE @Message NVARCHAR(400), @Delimiter CHAR(1)
    DECLARE @ParamTable TABLE ( ID INT IDENTITY(0,1), Parameter VARCHAR(1000) )
    Declare @startPos int, @endPos int
    SELECT @Message = @Format, @Delimiter = ','

    --handle first parameter
     set @endPos=CHARINDEX(@Delimiter,@Parameters)
    if (@endPos=0 and @Parameters is not null) --there is only one parameter
        insert into @ParamTable (Parameter) values(@Parameters)
    else begin
        insert into @ParamTable (Parameter) select substring(@Parameters,0,@endPos)
    end

    while @endPos>0
    Begin
        --insert a row for each parameter in the 
        set @startPos = @endPos + LEN(@Delimiter)
        set @endPos = CHARINDEX(@Delimiter,@Parameters, @startPos)
        if (@endPos>0)
            insert into @ParamTable (Parameter) select substring(@Parameters,@startPos,@endPos)
        else
            insert into @ParamTable (Parameter) select substring(@Parameters,@startPos,4000)            
    End

    UPDATE @ParamTable SET @Message = REPLACE ( @Message, '{'+CONVERT(VARCHAR,ID) + '}', Parameter )
    RETURN @Message
END
Go
    grant execute,references on dbo.formatString to public

Usage:

print dbo.formatString('hello {0}... you are {1}','world,good')
--result: hello world... you are good

At the moment this doesn't really exist (although you can of course write your own). There is an open connect bug for it: https://connect.microsoft.com/SQLServer/Feedback/Details/3130221, which as of this writing has just 1 vote.

Not exactly, but I would check out some of the articles on string handling (amongst other things) by "Phil Factor" (geddit?) on Simple Talk.

this is bad approach. you should work with assembly dll's, in which will do the same for you with better performance.

참고URL : https://stackoverflow.com/questions/159554/string-format-like-functionality-in-t-sql

'programing tip' 카테고리의 다른 글

CMake를 사용하여 DLL 파일을 실행 파일과 동일한 폴더에 복사하는 방법은 무엇입니까? (0)	2020.09.22
속성 선택기를 사용하여 클래스를 일치시키는 이유는 무엇입니까? (0)	2020.09.22
IIS7에서 교차 출처 리소스 공유 활성화 (0)	2020.09.22
일괄 정규화 및 드롭 아웃 순서? (0)	2020.09.22
SQLite에서 변수를 선언하고 사용 (0)	2020.09.22

현재글T-SQL의 기능과 같은 String.Format?

itbloger

T-SQL의 기능과 같은 String.Format?

T-SQL의 기능과 같은 String.Format?

'programing tip' 카테고리의 다른 글

'programing tip'의 다른글

티스토리툴바

T-SQL의 기능과 같은 String.Format?

T-SQL의 기능과 같은 String.Format?

'programing tip' 카테고리의 다른 글

'programing tip'의 다른글

관련글

티스토리툴바