String Format like functionality in T-SQL

Question

I m looking for a built-in function extended function in T-SQL for string manipulation similar to the String Format method in  NET

User · Answer

I think there is small correction while calculating end position.

Here is correct function

**>>**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

User · Answer

take a look at xp sprintf  example below   DECLARE  ret string varchar  255  EXEC xp sprintf  ret string OUTPUT        INSERT INTO  s VALUES   s   s     table1    1    2  PRINT  ret string   Result looks like this   INSERT INTO table1 VALUES  1  2      Just found an issue with the max size  255 char limit  of the string with this so there is an alternative function you can use   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   To get the same result as above you call the function as follows   print dbo fnSprintf  INSERT INTO  s VALUES   s   s     table1 1 2   default

User · Answer

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

User · Answer

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

User · Answer

One more idea    Although this is not a universal solution - it is simple and works  at least for me     For one placeholder  0    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   For two placeholders  0  and  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   For three placeholders  0    1  and  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   and so on     Such an approach allows us to use these functions in SELECT statement and with parameters of nvarchar  number  bit and datetime datatypes    For example   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

User · Answer

Actually there is no built in function similar to string Format function of  NET is available in SQL server    There is a function FORMATMESSAGE   in SQL server but it mimics to printf   function of C not string Format function of  NET   SELECT FORMATMESSAGE  This is the  s and this is the  s     first variable    second variable   AS Result

User · Answer

Not exactly  but I would check out some of the articles on string handling  amongst other things  by  Phil Factor   geddit   on Simple Talk

User · Answer

If you are using SQL Server 2012 and above  you can use FORMATMESSAGE  eg   DECLARE  s NVARCHAR 50     World   DECLARE  d INT   123  SELECT FORMATMESSAGE  Hello  s   d    s   d  -- RETURNS  Hello World  123      More examples from 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      NOTES    Undocumented in 2012 Limited to 2044 characters To escape the   sign  you need to double it   If you are logging errors in extended events  calling FORMATMESSAGE comes up as a  harmless  error

User · Answer

here s what I found with my experiments using the built-in  FORMATMESSAGE   function  sp addmessage  msgnum 50001  severity 1  msgText  Hello  s you are   d   replace  replace  SELECT FORMATMESSAGE 50001   Table1   5    when you call up sp addmessage  your message template gets stored into the system table master dbo sysmessages  verified on SQLServer 2000      You must manage addition and removal of template strings from the table  yourself  which is awkward if all you really want is output a quick message to the results screen   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 gt 0     Begin         --insert a row for each parameter in the          set  startPos    endPos   LEN  Delimiter          set  endPos   CHARINDEX  Delimiter  Parameters   startPos          if   endPos gt 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

User · Answer

Raw t-sql is limited to CHARINDEX    PATINDEX    REPLACE    and SUBSTRING   for string manipulation   But with sql server 2005 and later you can set up user defined functions that run in  Net  which means setting up a string format   UDF shouldn t be too tough

User · Answer

Here is my version  Can be extended to accommodate more number of parameters and can extend formatting based on type  Currently only date and datetime types are formatted   Example   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    Output   some string  abcd  some int 100 date 29-Apr-2017 some string  abcd  some int 100 date time 29-Apr-2017 19 40   Functions   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

User · Answer

There is a way  but it has its limitations   You can use the FORMATMESSAGE   function   It allows you to format a string using formatting similar to the printf   function in C     However  the biggest limitation is that it will only work with messages in the sys messages table   Here s an article about it  microsoft library ms186788  It s kind of a shame there isn t an easier way to do this  because there are times when you want to format a string varchar in the database   Hopefully you are only looking to format a string in a standard way and can use the sys messages table   Coincidentally  you could also use the RAISERROR   function with a very low severity  the documentation for raiseerror even mentions doing this  but the results are only printed   So you wouldn t be able to do anything with the resulting value  from what I understand    Good luck

User · Answer

I have created a user defined function to mimic the string format functionality  You can use it  stringformat-in-sql UPDATE  This version allows the user to change the delimitter  -- DROP function will loose the security settings  IF object id   dbo   svfn FormatString    IS NOT NULL     DROP FUNCTION  dbo   svfn FormatString  GO  CREATE FUNCTION  dbo   svfn FormatString         Format NVARCHAR 4000        Parameters NVARCHAR 4000        Delimiter CHAR 1          RETURNS NVARCHAR MAX  AS BEGIN                Name   dbo   svfn FormatString          Creation Date  12 18 2020          Purpose  Returns the formatted string  Just like in C-Sharp           Input Parameters     Format           The string to be Formatted                              Parameters       The comma separated list of parameters                              Delimiter        The delimitter to be used in the formatting process          Format               Format           N Hi  0   Welcome to our site  1   Thank you  0                                Parameters       N Karthik google com                               Delimiter                               Examples              SELECT dbo svfn FormatString N Hi  0   Welcome to our site  1   Thank you  0    N Karthik google com   default              SELECT dbo svfn FormatString N Hi  0   Welcome to our site  1   Thank you  0    N Karthik google com                   DECLARE  Message NVARCHAR 400      DECLARE  ParamTable TABLE   Id INT IDENTITY 0 1   Paramter VARCHAR 1000        SELECT  Message    Format       WITH CTE  StartPos  EndPos  AS               SELECT 1  CHARINDEX  Delimiter   Parameters          UNION ALL         SELECT EndPos    LEN  Delimiter    CHARINDEX  Delimiter   Parameters  EndPos    LEN  Delimiter            FROM CTE         WHERE EndPos  gt  0            INSERT INTO  ParamTable   Paramter       SELECT          Id    SUBSTRING  Parameters  StartPos  CASE WHEN EndPos  gt  0 THEN EndPos - StartPos ELSE 4000 END       FROM CTE      UPDATE  ParamTable      SET           Message   REPLACE  Message       CONVERT VARCHAR  Id         Paramter        RETURN  Message END

[tsql] String.Format like functionality in T-SQL?

Examples related to tsql

Examples related to string