Category Archives: Functions

TRIM function in Sql Server

Many a times we come across a scenario where we get an input string with leading and/or trailing spaces. In such scenarios we would like to store it into the database by removing the leading and trailing spaces. But Microsoft doesn’t have a built-in TRIM function which can remove both leading and Trailing spaces.

Instead it has LTRIM function which can be used to remove leading blanks and RTRIM function which can be used to remove trailing spaces. Let us understand these functions with examples and then see how we can nest these two functions together as shown in the below examples to remove leading and trailing spaces.

LTRIM Function

LTRIM function removes the leading spaces from the input string

DECLARE @StringToTrim VARCHAR(100) = '    String to trim    '
SELECT @StringToTrim 'String To Trim', 
	  DATALENGTH(@StringToTrim) 'Length of the string',
	  LTRIM(@StringToTrim) 'String trimmed by LTRIM', 
	  DATALENGTH(LTRIM(@StringToTrim)) 'Length of LTRIM string'

RESULT:
ltrim-in-sql-server

From the above result we can see that the LTRIM function is removing only the leading spaces. That is it has removed only the leading 4 spaces and not the trailing 4 spaces.

[ALSO READ] Difference between Len() and Datalength() functions in Sql Server

RTRIM Function

RTRIM function removes the trailing spaces from the input string

DECLARE @StringToTrim VARCHAR(100) = '    String to trim    '
SELECT @StringToTrim 'String To Trim', 
	  DATALENGTH(@StringToTrim) 'Length of the string',
	  RTRIM(@StringToTrim) 'String trimmed by RTRIM', 
	  DATALENGTH(RTRIM(@StringToTrim)) 'Length of RTRIM string'

RESULT:
rtrim-in-sql-server

From the above result we can see that the RTRIM function is removing only the trailing spaces. That is it has removed only the trailing 4 spaces and not the leading 4 spaces.

[ALSO READ] Usage of Function on Index Column in WHERE Caluse Leads to Index/Table Scan

TRIM Function

To remove the leading and trailing spaces we can nest the above LTRIM and RTRIM functions as shown in the below example:

DECLARE @StringToTrim VARCHAR(100) = '    String to trim    '
SELECT @StringToTrim 'String To Trim', 
 DATALENGTH(@StringToTrim) 'Length of the string',
 RTRIM(LTRIM(@StringToTrim)) 'String trimmed by LTRIM and RTRIM', 
 DATALENGTH(RTRIM(LTRIM(@StringToTrim)))
  AS 'Length of trimmed string'

RESULT:
trim-in-sql-server

From the above result we can see that the nested use of RTRIM and LTRIM functions has removed both the leading and trailing paces.. That is it has removed both the leading 4 spaces and the trailing 4 spaces from the input string.

We can create a user defined function like the below one. And use it wherever we want to remove both leading and trailing spaces.

CREATE	FUNCTION dbo.TRIM(@StringToTrim VARCHAR(MAX))
RETURNS	VARCHAR(MAX)
BEGIN 
	RETURN	RTRIM(LTRIM(@StringToTrim))
END

Below example shows how we can use the above created user defined TRIM function:

DECLARE @StringToTrim VARCHAR(100) = '    String to trim    '
SELECT dbo.TRIM( @StringToTrim ) 'Trimmed string',
 DATALENGTH(dbo.TRIM( @StringToTrim )) 
  AS 'Length of trimmed string'

RESULT:
trim-function-in-sql-server

[ALSO READ] 100 Frequently used queries in Sql Server – Part 1

DATETIMEFROMPARTS FUNCTION IN SQL SERVER 2012

DATETIMEFROMPARTS is one of the new built-in Date and Time Function introduced as a Part of Sql Server 2012. Returns a DATETIME value for the specified date and time. It is a Sql Server native function not dependent on the .NET CLR.

SYNTAX: DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds )

WHERE: year, month, day, hour, minute, seconds and milliseconds are integer expressions representing valid year, month, day, hour, minute, seconds and milliseconds respectively.
Return Type: DATETIME

Example 1: DATETIMEFROMPARTS – with valid date and time part integer constants

SELECT
 DATETIMEFROMPARTS(2013,6,23,1,20,14,8) AS 'DATETIME',
 DATETIMEFROMPARTS(1753,1,1,0,0,0,0) AS 'MIN DATETIME',
 DATETIMEFROMPARTS(9999,12,31,23,59,59,997) AS 'MAX DATETIME'

Result:

DATETIME                               MIN DATETIME                           MAX DATETIME
———————–                      ———————–                        ———————–
2013-06-23 01:20:14.007       1753-01-01 00:00:00.000        9999-12-31 23:59:59.997

Example 2: DATETIMEFROMPARTS – with valid date and time part integer variables

DECLARE @year INT = 2013, @month INT = 6, @day INT = 23,
        @hour INT = 1, @minute INT = 20, @seconds INT = 14, 
        @milliseconds INT = 878
SELECT DATETIMEFROMPARTS( @year, @month, @day, 
        @hour , @minute, @seconds, @milliseconds) AS 'DATETIME'

Result:

DATETIME
———————–
2013-06-23 01:20:14.877

Example 3: DATETIMEFROMPARTS – with Invalid part

DECLARE @year INT = 2013, @invalidmonth INT = 15, @day INT = 23,
        @hour INT = 1, @minute INT = 20, @seconds INT = 14, 
        @milliseconds INT = 878
SELECT DATETIMEFROMPARTS( @year, @invalidmonth, @day, 
        @hour , @minute, @seconds, @milliseconds) AS 'DATETIME'

Result:
Msg 289, Level 16, State 3, Line 3
Cannot construct data type datetime, some of the arguments have values which are not valid.

Example 4: DATETIMEFROMPARTS – with one of the part as NULL

DECLARE @year INT = 2013, @month INT = 6, @day INT = 23,
        @hour INT = 1, @minute INT = 20, @seconds INT = NULL, 
        @milliseconds INT = 878
SELECT DATETIMEFROMPARTS( @year, @month, @day, 
        @hour , @minute, @seconds, @milliseconds) AS 'DATETIME'

Result:
DATETIME
———————–
NULL

You may like to read the below new built-in function’s introduced in Sql Server 2012:

New Built in Functions introduced in Sql Server
CONVERSION FUNCTIONS
PARSE TRY_PARSE
TRY_CONVERT
STRING FUNCTIONS
CONCAT FORMAT
LOGICAL FUNCTIONS
CHOOSE IIF
DATE AND TIME FUNCTIONS
EOMONTH
DATEFROMPARTS DATETIMEFROMPARTS
SMALLDATETIMEFROMPARTS DATETIME2FROMPARTS
TIMEFROMPARTS DATETIMEOFFSETFROMPARTS

SMALLDATETIMEFROMPARTS FUNCTION IN SQL SERVER 2012

SMALLDATETIMEFROMPARTS is one of the new built-in Date and Time Function introduced as a Part of Sql Server 2012. Returns a SMALLDATETIME value for the specified date and time. It is a Sql Server native function not dependent on the .NET CLR.

SYNTAX: SMALLDATETIMEFROMPARTS ( year, month, day, hour, minute )

WHERE: year, month, day, hour and minute are integer expressions representing valid year, month, day, hour and minute respectively.
Return Type: SMALLDATETIME

Example 1: SMALLDATETIMEFROMPARTS – with valid date and time part integer constants

SELECT 
 SMALLDATETIMEFROMPARTS(2013,6,23,1,20 ) AS 'SMALLDATETIME',
 SMALLDATETIMEFROMPARTS(1900,1,1,0,0) AS 'MIN SMALLDATETIME',
 SMALLDATETIMEFROMPARTS(2079,6,6,23,59) AS 'MAX SMALLDATETIME'

Result:

SMALLDATETIME         MIN SMALLDATETIME         MAX SMALLDATETIME
———————–           ———————–                   ———————–
2013-06-23 01:20:00   1900-01-01 00:00:00             2079-06-06 23:59:00

Example 2: SMALLDATETIMEFROMPARTS – with valid date and time part integer variables

DECLARE @year INT = 2013, @month INT = 6, @day INT = 23,
		@hour INT = 1, @minute INT = 20
SELECT SMALLDATETIMEFROMPARTS( @year, @month, @day, 
		@hour , @minute) AS 'SMALLDATETIME'

Result:

SMALLDATETIME
———————–
2013-06-23 01:20:00

Example 3: SMALLDATETIMEFROMPARTS – with Invalid part

DECLARE @year INT = 2013, @invalidmonth INT = 15, @day INT = 23,
		@hour INT = 1, @minute INT = 20
SELECT SMALLDATETIMEFROMPARTS( @year, @invalidmonth, @day, 
		@hour , @minute) AS 'SMALLDATETIME'

Result:
Msg 289, Level 16, State 4, Line 3
Cannot construct data type smalldatetime, some of the arguments have values which are not valid.

Example 4: SMALLDATETIMEFROMPARTS – with one of the part as NULL

DECLARE @year INT = 2013, @invalidmonth INT = 15, @day INT = 23,
		@hour INT = 1, @minute INT = NULL
SELECT SMALLDATETIMEFROMPARTS( @year, @invalidmonth, @day, 
		@hour , @minute) AS 'SMALLDATETIME'

Result:
SMALLDATETIME
———————–
NULL

You may like to read the below new built-in function’s introduced in Sql Server 2012:

New Built in Functions introduced in Sql Server
CONVERSION FUNCTIONS
PARSE TRY_PARSE
TRY_CONVERT
STRING FUNCTIONS
CONCAT FORMAT
LOGICAL FUNCTIONS
CHOOSE IIF
DATE AND TIME FUNCTIONS
EOMONTH
DATEFROMPARTS DATETIMEFROMPARTS
SMALLDATETIMEFROMPARTS DATETIME2FROMPARTS
TIMEFROMPARTS DATETIMEOFFSETFROMPARTS

DATEFROMPARTS FUNCTION IN SQL SERVER 2012

DATEFROMPARTS is one of the new built-in Date and Time Function introduced as a Part of Sql Server 2012. As name suggests it forms a DATE from it’s parts i.e. Year, Month and Day. It is a Sql Server native function not dependent on the .NET CLR.

SYNTAX: DATEFROMPARTS ( year, month, day )

WHERE: year, month and day are integer expressions representing valid year, month and day respectively.
Return Type: DATE

Example 1: DATEFROMPARTS – with valid date part integer constants

SELECT DATEFROMPARTS( 2013, 6, 23 ) AS 'DATE',
	DATEFROMPARTS( 1, 1, 1 ) AS 'MIN DATE',
	DATEFROMPARTS( 9999, 12, 31 ) AS 'MAX DATE'

Result:
DATE                    MIN DATE                 MAX DATE
———-                ———-                       ———-
2013-06-23       0001-01-01              9999-12-31

Example 2: DATEFROMPARTS – with valid date part integer variables

DECLARE @year INT = 2013, @month INT = 6, @day INT = 23
SELECT DATEFROMPARTS( @year, @month, @day) AS 'DATE'

Result:
DATE
———-
2013-06-23

Example 3: DATEFROMPARTS – with invalid month part

DECLARE @year INT = 2013, @invalidmonth INT = 15, @day INT = 23
SELECT DATEFROMPARTS( @year, @invalidmonth, @day) AS 'DATE'

Result:
Msg 289, Level 16, State 1, Line 2
Cannot construct data type date, some of the arguments have values which are not valid.

Example 4: DATEFROMPARTS – with one of the part as NULL

DECLARE @year INT = 2013, @month INT = 6, @day INT = NULL
SELECT DATEFROMPARTS( @year, @month, @day) AS 'DATE'

Result:
DATE
———-
NULL

You may like to read the below new built-in function’s introduced in Sql Server 2012:

New Built in Functions introduced in Sql Server
CONVERSION FUNCTIONS
PARSE TRY_PARSE
TRY_CONVERT
STRING FUNCTIONS
CONCAT FORMAT
LOGICAL FUNCTIONS
CHOOSE IIF
DATE AND TIME FUNCTIONS
EOMONTH
DATEFROMPARTS DATETIMEFROMPARTS
SMALLDATETIMEFROMPARTS DATETIME2FROMPARTS
TIMEFROMPARTS DATETIMEOFFSETFROMPARTS

FORMAT STRING FUNCTION IN SQL SERVER 2012

FORMAT is one of the new built-in String Function introduced as a Part of Sql Server 2012. It returns the value formatted in the specified format using the optional culture parameter value. It is not an Sql Server native function instead it is .NET CLR dependent function.

SYNTAX: FORMAT ( value, format [, culture ] )

Parameter Description
value: Value to be formatted
format: This parameter specifies the format in which the vlaue will be formatted.
culture: This parameter is optional, it specifies the culture in which the value is formatted. If it is not specified then the language of the current session is used.

RETURNS: Return value type is nvarchar.

Example 1: FORMAT DATE with Culture

DECLARE @date DATETIME = GETDATE() 
SELECT @date AS 'GETDATE()',
       FORMAT( @date, 'd', 'en-US') AS 'DATE IN US Culture',
       FORMAT( @date, 'd', 'en-IN') AS 'DATE IN INDIAN Culture',
       FORMAT( @date, 'd', 'de-DE') AS 'DATE IN GERMAN Culture'

Result:

FORMAT_FUNCTION_IN_SQL_SERVER_2012_1

Example 2: FORMAT CURRENCY with Culture

DECLARE @Price INT = 40
SELECT FORMAT(@Price,'c','en-US') 
         AS 'CURRENCY IN US Culture',       
    FORMAT(@Price,'c','de-DE')
         AS 'CURRENCY IN GERMAN Culture'

Result:

FORMAT_FUNCTION_IN_SQL_SERVER_2012_2

Example 3: FORMAT CURRENCY

DECLARE @Price DECIMAL(5,3) = 40.356
SELECT FORMAT( @Price, 'C') AS 'Default',
      FORMAT( @Price, 'C0') AS 'With 0 Decimal',
       FORMAT( @Price, 'C1') AS 'With 1 Decimal',
       FORMAT( @Price, 'C2') AS 'With 2 Decimal',
       FORMAT( @Price, 'C3') AS 'With 3 Decimal'

Result:
FORMAT_FUNCTION_IN_SQL_SERVER_2012_3

Example 4: FORMAT PERCENTAGE

DECLARE @Percentage float = 0.35674
SELECT FORMAT( @Percentage, 'P') AS '% Default',
       FORMAT( @Percentage, 'P0') AS '% With 0 Decimal',
       FORMAT( @Percentage, 'P1') AS '% with 1 Decimal',
       FORMAT( @Percentage, 'P2') AS '% with 2 Decimal',
       FORMAT( @Percentage, 'P3') AS '% with 3 Decimal'

Result:
FORMAT_FUNCTION_IN_SQL_SERVER_2012_4

Example 5: FORMAT NUMBER

DECLARE @Number AS DECIMAL(10,2) = 454545.389
SELECT FORMAT( @Number, 'N','en-US') AS 'Number Format in US',
    FORMAT( @Number, 'N','en-IN')  AS 'Number Format in INDIA'

SELECT FORMAT( @Number, '#.0')     AS 'With 1 Decimal',
    FORMAT( @Number, '#.00')    AS 'With 2 Decimal',
    FORMAT( @Number, '#,##.00') AS 'With Comma and 2 Decimal',
    FORMAT( @Number, '##.00')   AS 'Without Comma and 2 Decimal'

Result:
FORMAT_FUNCTION_IN_SQL_SERVER_2012_5

Example 6: CUSTOM DATE FORMATS

DECLARE @date DATETIME = GETDATE() 
SELECT @date AS 'GETDATE()',
    FORMAT ( @date, 'dd/MM/yyyy') AS 'dd/MM/yyyy',
    FORMAT ( @date, 'MM/dd/yyyy') AS 'MM/dd/yyyy',
    FORMAT ( @date, 'yyyy/MM/dd') AS 'yyyy/MM/dd' 

SELECT 
 FORMAT( @date,'dddd, MMMM dd, yyyy hh:mm:ss tt','en-US')
   AS 'US',
 FORMAT( @date,'dddd, MMMM dd, yyyy hh:mm:ss tt','hi-IN')
   AS 'Hindi',
 FORMAT( @date,'dddd, MMMM dd, yyyy hh:mm:ss tt','kn-IN')
  AS 'Kannada'

Result:

FORMAT_FUNCTION_IN_SQL_SERVER_2012_6

DECLARE @date DATETIME = GETDATE()
SELECT FORMAT ( @date, 'dd', 'en-US' ) AS 'US',
	FORMAT ( @date, 'ddd', 'en-US' )   AS 'US',
	FORMAT ( @date, 'dddd', 'en-US' )  AS 'US',
	FORMAT ( @date, 'dddd', 'kn-IN' )  AS 'Kannada',
	FORMAT ( @date, 'dddd', 'hi-IN' )  AS 'Hindi'

SELECT FORMAT ( @date, 'M', 'en-US' )  AS 'US',
	FORMAT ( @date, 'MM', 'en-US' )    AS 'US',
	FORMAT ( @date, 'MMM', 'en-US' )   AS 'US',
	FORMAT ( @date, 'MMMM', 'en-US' )  AS 'US',
	FORMAT ( @date, 'MMMM', 'kn-IN' )  AS 'Kannada',
	FORMAT ( @date, 'MMMM', 'hi-IN' )  AS 'Hindi'

SELECT FORMAT ( @date, 'y', 'en-US' )   AS 'US',
       FORMAT ( @date, 'y', 'kn-IN' )   AS 'Kannada',
       FORMAT ( @date, 'y', 'hi-IN' )   AS 'Hindi',
       FORMAT ( @date, 'yy', 'en-US' )  AS 'US',
       FORMAT ( @date, 'yyy', 'en-US' ) AS 'US'

Result:

FORMAT_FUNCTION_IN_SQL_SERVER_2012_7

Example 7: Invalid Culture

DECLARE @date DATETIME = GETDATE()
SELECT FORMAT(@date,'d','Test') AS 'Invalid Culture'

Result:
Msg 9818, Level 16, State 1, Line 2
The culture parameter ‘Test’ provided in the function call is not supported.

You may like to read the below new built-in function’s introduced in Sql Server 2012:

New Built in Functions introduced in Sql Server
CONVERSION FUNCTIONS
PARSE TRY_PARSE
TRY_CONVERT
STRING FUNCTIONS
CONCAT FORMAT
LOGICAL FUNCTIONS
CHOOSE IIF
DATE AND TIME FUNCTIONS
EOMONTH
DATEFROMPARTS DATETIMEFROMPARTS
SMALLDATETIMEFROMPARTS DATETIME2FROMPARTS
TIMEFROMPARTS DATETIMEOFFSETFROMPARTS