none
INTRODUCING NEW CONCAT FUNCTION IN SQL SERVER 2012! RRS feed

  • 常规讨论

  • 引自: http://sqlcommunity.com/ArticleLibrary/tabid/65/EntryId/42/INTRODUCING-NEW-CONCAT-FUNCTION-IN-SQL-SERVER-2012.aspx

    SQL Server 2012 introduces a brand new string function called CONCAT(). CONCAT() string function allows you to concatenate up to 255 string or variable values in to one single string. It requires a minimum of two input values when calling the function. CONCAT takes care of implicitly converting the data values to a string by following the data type conversion rules of SQL Server 2012. This eliminates the need of explicit data conversions when concatenating two values.

    Note: NULL values are implicitly converted to an empty string. If all the variables passed as parameters in CONCAT function are NULL, an empty string of type VARCHAR(1) is returned.

    SYNTAX:

    SELECT CONCAT (String_Value1, String_Value2, String_Value3 [, String_ValueN]) -- Between 2 and 254 values can be passed.

     

    TEST 1 - CONCATENATION WITH CONCAT()

    TEST 2 - CONCATENATION WITHOUT CONCAT()

    Declare @a Varchar(100)='Microsoft SQL Server is now '

    Declare @b int=22

    Declare @c varchar(200)=' years old'

    Select CONCAT (@a, @b, @c)

    Go

     

    Returns:

    Microsoft SQL Server is now 22 Years old

     

    Declare @a Varchar(100)='Microsoft SQL Server is now '

    Declare @b int=22

    Declare @c varchar(200)=' years old'

    Select @a+Convert(varchar,(@b))+@c

    Go;

     

    Returns:

    Microsoft SQL Server is now 22 Years old

     

    In the above example, observe the use of CONCAT() function. There’s no data conversion being performed if you are using CONCAT function. However, in the second test, we are having to use data conversion function to convert Integer value to a string.

    The data returned by CONCAT function depends on the type of values passed. The below table shows the mapping of input and output types:

    Input Value / Type

    Output Value / Type

    SQL-CLR (TYPES & UDT)

    NVARCHAR(MAX)

    NVARCHAR(MAX)

    NVARCHAR(MAX)

    NVARCHAR(<=4000)

    NVARCHAR(<=4000)

    VARBINARY(MAX)

    NVARCHAR(MAX)

    ALL OTHER INPUT TYPES

    VARCHAR(<=8000) *Unless one of the parameters is an NVARCHAR of any length and in that case, the output value would be in NVARCHAR(MAX)

    NOTE: CONCAT function only works with SQL Server 2012 and above versions. However, to make this work in older versions of SQL Server, you will need to create your own custom function that can take care of concatenating values and appropriately converting their data types.


    Mark as Answer if it helps. This posting is provided "AS IS" with no warranties, confers no rights.
    2012年1月11日 9:54

全部回复