String Functions All Versions

Examples

• 10

  Quotename

  edit move example delete share

  10

  Returns a Unicode string surrounded by delimiters to make it a valid SQL Server delimited identifier.

  Parameters:

  1. character string. A string of Unicode data, up to 128 characters (sysname). If an input string is longer than 128 characters function returns null.
  2. quote character. Optional. A single character to use as a delimiter. Can be a single quotation mark (' or ``), a left or right bracket ({,[,(,< or >,),],}) or a double quotation mark ("). Any other value will return null. Default value is square brackets.

  SELECT QUOTENAME('what''s my name?')      -- Returns [what's my name?]
  
  SELECT QUOTENAME('what''s my name?', '[') -- Returns [what's my name?]
  SELECT QUOTENAME('what''s my name?', ']') -- Returns [what's my name?]
  
  SELECT QUOTENAME('what''s my name?', '''') -- Returns 'what''s my name?'
  
  SELECT QUOTENAME('what''s my name?', '"') -- Returns "what's my name?"
  
  SELECT QUOTENAME('what''s my name?', ')') -- Returns (what's my name?)
  SELECT QUOTENAME('what''s my name?', '(') -- Returns (what's my name?)
  
  SELECT QUOTENAME('what''s my name?', '<') -- Returns <what's my name?>
  SELECT QUOTENAME('what''s my name?', '>') -- Returns <what's my name?>
  
  SELECT QUOTENAME('what''s my name?', '{') -- Returns {what's my name?}
  SELECT QUOTENAME('what''s my name?', '}') -- Returns {what's my name?}
  
  SELECT QUOTENAME('what''s my name?', '`') -- Returns `what's my name?`
  

  edited Aug 11 '16 at 15:50

  

  

  
• 7

  Replace

  edit move example delete share

  7

  Returns a string (varchar or nvarchar) where all occurrences of a specified sub string is replaced with another sub string.

  Parameters:

  1. string expression. This is the string that would be searched. It can be a character or binary data type.
  2. pattern. This is the sub string that would be replaced. It can be a character or binary data type. The pattern argument cannot be an empty string.
  3. replacement. This is the sub string that would replace the pattern sub string. It can be a character or binary data.

  SELECT REPLACE('This is my string', 'is', 'XX') -- Returns 'ThXX XX my string'.
  

  Note: If string expression is not of type varchar(max) or nvarchar(max), the replace function truncates the return value at 8,000 chars.

  created Jul 27 '16 at 16:15

  
• 6

  String_Split

  edit move example delete share

  6

  SQL Server 2016

  Splits a string expression using a character separator. Note that STRING_SPLIT() is a table-valued function and therefore must be used within FROM clause.

  Parameters:

  1. string. Any character type expression (char, nchar, varchar or nvarchar)
  2. seperator. A single character expression of any type (char(1), nchar(1), varchar(1) or nvarchar(1)).

  Returns a single column table where each row contains a fragment of the string. The name of the columns is value, and the datatype is nvarchar if any of the parameters is either nchar or nvarchar, otherwise varchar.

  The following example splits a string using space as a separator:

  SELECT value FROM STRING_SPLIT('Lorem ipsum dolor sit amet.', ' ');
  

  Result:

  value
  -----
  Lorem
  ipsum
  dolor
  sit
  amet.
  

  Remarks:

  The STRING_SPLIT function is available only under compatibility level 130. If your database compatibility level is lower than 130, SQL Server will not be able to find and execute STRING_SPLIT function. You can change the compatibility level of a database using the following command:

  ALTER DATABASE [database_name] SET COMPATIBILITY_LEVEL = 130
  

  SQL Server 2016

  Older versions of sql server does not have a built in split string function. There are many user defined functions that handles the problem of splitting a string. You can read Aaron Bertrand's article Split strings the right way – or the next best way for a comprehensive comparison of some of them.

  edited Aug 5 '16 at 9:18

  

  

  
• 6

  Substring

  edit move example delete share

  6

  Returns a substring that starts with the char that's in the specified start index and the specified max length.

  Parameters:

  1. Character expression. The character expression can be of any data type that can be implicitly converted to varchar or nvarchar, except for text or ntext.
  2. Start index. A number (int or bigint) that specifies the start index of the requested substring. (Note: strings in sql server are base 1 index, meaning that the first character of the string is index 1). This number can be less then 1. In this case, If the sum of start index and max length is greater then 0, the return string would be a string starting from the first char of the character expression and with the length of (start index + max length - 1). If it's less then 0, an empty string would be returned.
  3. Max length. An integer number between 0 and bigint max value (9,223,372,036,854,775,807). If the max length parameter is negative, an error will be raised.

  SELECT SUBSTRING('This is my string', 6, 5) -- returns 'is my'
  

  If the max length + start index is more then the number of characters in the string, the entier string is returned.

  SELECT SUBSTRING('Hello World',1,100) -- returns 'Hello World'
  

  If the start index is bigger then the number of characters in the string, an empty string is returned.

  SELECT SUBSTRING('Hello World',15,10) -- returns ''
  

  edited Nov 24 '16 at 9:32

  

  

  
• 5

  Left

  edit move example delete share

  5

  Returns a sub string starting with the left most char of a string and up to the maximum length specified.

  Parameters:

  1. character expression. The character expression can be of any data type that can be implicitly converted to varchar or nvarchar, except for text or ntext
  2. max length. An integer number between 0 and bigint max value (9,223,372,036,854,775,807).
     If the max length parameter is negative, an error will be raised.

  SELECT LEFT('This is my string', 4) -- result: 'This'
  

  If the max length is more then the number of characters in the string, the entier string is returned.

  SELECT LEFT('This is my string', 50) -- result: 'This is my string'
  

  edited Nov 19 '16 at 19:39

  

  
• 4

  Format

  edit move example delete share

  4

  SQL Server 2012

  Returns a NVARCHAR value formatted with the specified format and culture (if specified). This is primarily used for converting date-time types to strings.

  Parameters:

  1. value. An expression of a supported data type to format. valid types are listed below.
  2. format. An NVARCHAR format pattern. See Microsoft official documentation for standard and custom format strings.
  3. culture. Optional. nvarchar argument specifying a culture. The default value is the culture of the current session.

  DATE

  Using standard format strings:

  DECLARE @d DATETIME = '2016-07-31';  
  
  SELECT 
      FORMAT ( @d, 'd', 'en-US' ) AS 'US English Result' -- Returns '7/31/2016'
     ,FORMAT ( @d, 'd', 'en-gb' ) AS 'Great Britain English Result' -- Returns '31/07/2016'
     ,FORMAT ( @d, 'd', 'de-de' ) AS 'German Result' -- Returns '31.07.2016'
     ,FORMAT ( @d, 'd', 'zh-cn' ) AS 'Simplified Chinese (PRC) Result' -- Returns '2016/7/31'
     ,FORMAT ( @d, 'D', 'en-US' ) AS 'US English Result' -- Returns 'Sunday, July 31, 2016'
     ,FORMAT ( @d, 'D', 'en-gb' ) AS 'Great Britain English Result' -- Returns '31 July 2016'
     ,FORMAT ( @d, 'D', 'de-de' ) AS 'German Result' -- Returns 'Sonntag, 31. Juli 2016'
  

  Using custom format strings:

  SELECT FORMAT( @d, 'dd/MM/yyyy', 'en-US' ) AS 'DateTime Result' -- Returns '31/07/2016'
        ,FORMAT(123456789,'###-##-####') AS 'Custom Number Result' -- Returns '123-45-6789',
        ,FORMAT( @d,'dddd, MMMM dd, yyyy hh:mm:ss tt','en-US') AS 'US' -- Returns 'Sunday, July 31, 2016 12:00:00 AM'
        ,FORMAT( @d,'dddd, MMMM dd, yyyy hh:mm:ss tt','hi-IN') AS 'Hindi' -- Returns रविवार, जुलाई 31, 2016 12:00:00 पूर्वाह्न
        ,FORMAT ( @d, 'dddd', 'en-US' )  AS 'US' -- Returns 'Sunday'
        ,FORMAT ( @d, 'dddd', 'hi-IN' )  AS 'Hindi' -- Returns 'रविवार'
  

  FORMAT can also be used for formatting CURRENCY,PERCENTAGE and NUMBERS.

  CURRENCY

  DECLARE @Price1 INT = 40
  SELECT FORMAT(@Price1,'c','en-US') AS 'CURRENCY IN US Culture' -- Returns '$40.00'      
         ,FORMAT(@Price1,'c','de-DE') AS 'CURRENCY IN GERMAN Culture' -- Returns '40,00 €'
  

  We can specify the number of digits after the decimal.

  DECLARE @Price DECIMAL(5,3) = 40.356
  SELECT FORMAT( @Price, 'C') AS 'Default', -- Returns '$40.36'
         FORMAT( @Price, 'C0') AS 'With 0 Decimal', -- Returns '$40'
         FORMAT( @Price, 'C1') AS 'With 1 Decimal', -- Returns '$40.4'
         FORMAT( @Price, 'C2') AS 'With 2 Decimal', -- Returns '$40.36'
  

  PERCENTAGE

     DECLARE @Percentage float = 0.35674
     SELECT FORMAT( @Percentage, 'P') AS '% Default', -- Returns '35.67 %'
     FORMAT( @Percentage, 'P0') AS '% With 0 Decimal', -- Returns '36 %'
     FORMAT( @Percentage, 'P1') AS '% with 1 Decimal'  -- Returns '35.7 %'
  

  NUMBER

  DECLARE @Number AS DECIMAL(10,2) = 454545.389
  SELECT FORMAT( @Number, 'N','en-US') AS 'Number Format in US', -- Returns '454,545.39'
  FORMAT( @Number, 'N','en-IN')  AS 'Number Format in INDIA', -- Returns '4,54,545.39'
  FORMAT( @Number, '#.0')     AS 'With 1 Decimal', -- Returns '454545.4'
  FORMAT( @Number, '#.00')    AS 'With 2 Decimal', -- Returns '454545.39'
  FORMAT( @Number, '#,##.00') AS 'With Comma and 2 Decimal', -- Returns '454,545.39'
  FORMAT( @Number, '##.00')   AS 'Without Comma and 2 Decimal' -- Returns '454545.39'
  

  Valid value types list: (source)

  Category         Type             .Net type
  -------------------------------------------
  Numeric          bigint           Int64
  Numeric          int              Int32
  Numeric          smallint         Int16
  Numeric          tinyint          Byte
  Numeric          decimal          SqlDecimal
  Numeric          numeric          SqlDecimal
  Numeric          float            Double
  Numeric          real             Single
  Numeric          smallmoney       Decimal
  Numeric          money            Decimal
  Date and Time    date             DateTime
  Date and Time    time             TimeSpan
  Date and Time    datetime         DateTime
  Date and Time    smalldatetime    DateTime
  Date and Time    datetime2        DateTime
  Date and Time    datetimeoffset   DateTimeOffset
  

  Important Notes:

  • FORMAT returns NULL for errors other than a culture that is not valid. For example, NULL is returned if the value specified in format is not valid.
  • FORMAT relies on the presence of the .NET Framework Common Language Runtime (CLR).
  • FORMAT relies upon CLR formatting rules which dictate that colons and periods must be escaped. Therefore, when the format string (second parameter) contains a colon or period, the colon or period must be escaped with backslash when an input value (first parameter) is of the time data type.

  See also Date & Time Formatting using FORMAT documentation example.

  edited Sep 16 '16 at 6:30

  

  

  
• 3

  ASCII

  edit move example delete share

  3

  Returns an int value representing the ASCII code of the leftmost character of a string.

  SELECT ASCII('t') -- Returns 116
  SELECT ASCII('T') -- Returns 84
  SELECT ASCII('This') -- Returns 84
  

  If the string is Unicode and the leftmost character is not ASCII but representable in the current collation, a value greater than 127 can be returned:

  SELECT ASCII(N'ï') -- returns 239 when `SERVERPROPERTY('COLLATION') = 'SQL_Latin1_General_CP1_CI_AS'`
  

  If the string is Unicode and the leftmost character cannot be represented in the current collation, the int value of 63 is returned: (which represents question mark in ASCII):

  SELECT ASCII(N'߷') -- returns 63
  SELECT ASCII(nchar(2039)) -- returns 63
  

  edited Sep 12 '16 at 20:31

  

  
• 3

  Char

  edit move example delete share

  3

  Returns a char represented by an int ASCII code.

  SELECT CHAR(116) -- Returns 't'
  SELECT CHAR(84)  -- Returns 'T'
  

  This can be used to introduce new line/line feed CHAR(10), carriage returns CHAR(13), etc. See AsciiTable.com for reference.

  If the argument value is not between 0 and 255, the CHAR function returns NULL.
  The return data type of the CHAR function is char(1)

  edited Jul 28 '16 at 19:00

  

  
• 3

  Concat

  edit move example delete share

  3

  SQL Server 2012

  Returns a string that is the result of two or more strings joined together. CONCAT accepts two or more arguments.

  SELECT CONCAT('This', ' is', ' my', ' string') -- returns 'This is my string'
  

  Note: Unlike concatenating strings using the string concatenation operator (+), when passing a null value to the concat function it will implicitly convert it to an empty string:

  SELECT CONCAT('This', NULL, ' is', ' my', ' string'), -- returns 'This is my string'
         'This' + NULL + ' is' + ' my' + ' string' -- returns NULL.
  

  Also arguments of a non-string type will be implicitly converted to a string:

  SELECT CONCAT('This', ' is my ', 3, 'rd string') -- returns 'This is my 3rd string'
  

  Non-string type variables will also be converted to string format, no need to manually covert or cast it to string:

  DECLARE @Age INT=23;
  SELECT CONCAT('Ram is ', @Age,' years old');  -- returns 'Ram is 23 years old'
  

  SQL Server 2012

  Older versions do not support CONCAT function and must use the string concatenation operator (+) instead. Non-string types must be cast or converted to string types in order to concatenate them this way.

  SELECT 'This is the number ' + CAST(42 AS VARCHAR(5)) --returns 'This is the number 42'
  

  edited Sep 16 '16 at 14:22

  

  

  

  

  
• 3

  Difference

  edit move example delete share

  3

  Returns an integer (int) value that indicates the difference between the soundex values of two character expressions.

  Parameters:

  1. character expression 1.
  2. character expression 2.

  Both parameters are alphanumeric expressions of character data.

  The integer returned is the number of chars in the soundex values of the parameters that are the same, so 4 means that the expressions are very similar and 0 means that they are very different.

  SELECT  SOUNDEX('Green'),  -- G650
          SOUNDEX('Greene'),  -- G650
          DIFFERENCE('Green','Greene') -- Returns 4
          
  SELECT  SOUNDEX('Blotchet-Halls'),  -- B432
          SOUNDEX('Greene'),  -- G650
          DIFFERENCE('Blotchet-Halls', 'Greene') -- Returns 0
  

  created Aug 1 '16 at 23:52

  
• 3

  Soundex

  edit move example delete share

  3

  Returns a four-character code (varchar) to evaluate the phonetic similarity of two strings.

  Parameters:

  1. character expression. An alphanumeric expression of character data.

  The soundex function creates a four-character code that is based on how the character expression would sound when spoken. the first char is the the upper case version of the first character of the parameter, the rest 3 characters are numbers representing the letters in the expression (except a, e, i, o, u, h, w and y that are ignored).

  SELECT SOUNDEX ('Smith') -- Returns 'S530'
  
  SELECT SOUNDEX ('Smythe') -- Returns 'S530'
  

  edited Aug 31 '16 at 13:42

  

  
• 3

  Space

  edit move example delete share

  3

  Returns a string (varchar) of repeated spaces.

  Parameters:

  1. integer expression. Any integer expression, up to 8000. If negative, null is returned. if 0, an empty string is returned. (To return a string longer then 8000 spaces, use Replicate.

  SELECT SPACE(-1) -- Returns NULL
  SELECT SPACE(0)  -- Returns an empty string
  SELECT SPACE(3)  -- Returns '   ' (a string containing 3 spaces)
  

  edited Aug 3 '16 at 10:02

  

  
• 3

  String_escape

  edit move example delete share

  3

  SQL Server 2016

  Escapes special characters in texts and returns text (nvarchar(max)) with escaped characters.

  Parameters:

  1. text. is a nvarchar expression representing the string that should be escaped.

  2. type. Escaping rules that will be applied. Currently the only supported value is 'json'.

  SELECT STRING_ESCAPE('\   /  
  \\    "     ', 'json') -- returns '\\\t\/\n\\\\\t\"\t'
  

  List of characters that will be escaped:

  Special character    Encoded sequence
  -------------------------------------
  Quotation mark (")   \"
  Reverse solidus (\)  \\
  Solidus (/)          \/
  Backspace            \b
  Form feed            \f
  New line             \n
  Carriage return      \r
  Horizontal tab       \t
  
  
  Control character    Encoded sequence
  ------------------------------------
  CHAR(0)            \u0000
  CHAR(1)            \u0001
  ...                ...
  CHAR(31)           \u001f
  

  created Aug 3 '16 at 15:41

  
• 2

  CharIndex

  edit move example delete share

  2

  Returns the start index of a the first occurrence of string expression inside another string expression.

  Parameters list:

  1. String to find (up to 8000 chars)
  2. String to search (any valid character data type and length, including binary)
  3. (Optional) index to start. A number of type int or big int. If omitted or less then 1, the search starts at the beginning of the string.

  If the string to search is varchar(max), nvarchar(max) or varbinary(max), the CHARINDEX function will return a bigint value. Otherwise, it will return an int.

  SELECT CHARINDEX('is', 'this is my string') -- returns 3
  SELECT CHARINDEX('is', 'this is my string', 4) -- returns 6
  SELECT CHARINDEX(' is', 'this is my string') -- returns 5
  

  created Jul 26 '16 at 21:19

  
• 2

  Len

  edit move example delete share

  2

  Returns the number of characters of a string.
  Note: the LEN function ignores trailing spaces:

  SELECT LEN('My string'), -- returns 9
         LEN('My string   '), -- returns 9
         LEN('   My string') -- returns 12
  

  If the length including trailing spaces is desired there are several techniques to achieve this, although each has its drawbacks. One technique is to append a single character to the string, and then use the LEN minus one:

  DECLARE @str varchar(100) = 'My string   '
  SELECT LEN(@str + 'x') - 1 -- returns 12
  

  The drawback to this is if the type of the string variable or column is of the maximum length, the append of the extra character is discarded, and the resulting length will still not count trailing spaces.

  Another technique is to use theDATALENGTH function.

  DECLARE @str varchar(100) = 'My string   '
  SELECT DATALENGTH(@str) -- returns 12
  

  It's important to note though that DATALENGTH returns the length in bytes of the string in memory. This will be different for varchar vs. nvarchar.

  DECLARE @str nvarchar(100) = 'My string   '
  SELECT DATALENGTH(@str) -- returns 24
  

  You can adjust for this by dividing the datalength of the string by the datalength of a single character (which must be of the same type). The example below does this, and also handles the case where the target string happens to be empty, thus avoiding a divide by zero.

  DECLARE @str nvarchar(100) = 'My string   '
  SELECT DATALENGTH(@str) / DATALENGTH(LEFT(LEFT(@s, 1) + 'x', 1)) -- returns 12
  

  Even this, though, is not guaranteed to work in SQL Server 2012 and above when the string contains surrogate pairs (some characters can occupy more bytes than other characters in the same string).

  edited Sep 14 '16 at 4:42

  

  

  
• 2

  Lower

  edit move example delete share

  2

  Returns a character expression (varchar or nvarchar) after converting all uppercase characters to lowercase.

  Parameters:

  1. Character expression. Any expression of character or binary data that can be implicitly converted to varchar.

  SELECT LOWER('This IS my STRING') -- Returns 'this is my string'
  
  DECLARE @String nchar(17) = N'This IS my STRING';
  SELECT LOWER(@String) -- Returns 'this is my string'
  

  created Jul 26 '16 at 21:19

  
• 2

  LTrim

  edit move example delete share

  2

  Returns a character expression (varchar or nvarchar) after removing all leading white spaces, i.e., white spaces from the left through to the first non-white space character.

  Parameters:

  1. character expression. Any expression of character or binary data that can be implicitly converted to varcher, except text, ntext and image.

  SELECT LTRIM('    This is my string') -- Returns 'This is my string'
  

  edited Aug 4 '16 at 4:17

  

  
• 2

  NChar

  edit move example delete share

  2

  Returns the Unicode character(s) (nchar(1) or nvarchar(2)) corresponding to the integer argument it receives, as defined by the Unicode standard.

  Parameters:

  1. integer expression. Any integer expression that is a positive number between 0 and 65535, or if the collation of the database supports supplementary character (CS) flag, the supported range is between 0 to 1114111. If the integer expression does not fall inside this range, null is returned.

  SELECT NCHAR(257) -- Returns 'ā'
  SELECT NCHAR(400) -- Returns 'Ɛ'
  

  created Jul 26 '16 at 21:19

  
• 2

  PatIndex

  edit move example delete share

  2

  Returns the starting position of the first occurrence of a the specified pattern in the specified expression.

  Parameters:

  1. pattern. a character expression the contains the sequence to be found. Wildcards can be used, however the % wildcard must start and end the pattern, otherwise it will only return a result if the pattern is at the beginning or end of the expression. Limited to A maximum length of 8000 chars.

  2. expression. Any string data type.

  SELECT PATINDEX('%ter%', 'interesting') -- Returns 3. 
  
  SELECT PATINDEX('%t_r%t%', 'interesting') -- Returns 3. 
  
  SELECT PATINDEX('ter%', 'interesting') -- Returns 0. 
  
  SELECT PATINDEX('inter%', 'interesting') -- Returns 1. 
  
  SELECT PATINDEX('%ing', 'interesting') -- Returns 9. 
  

  edited Jul 27 '16 at 16:15

  

  
• 2

  Replicate

  edit move example delete share

  2

  Repeats a string value a specified number of times.

  Parameters:

  1. string expression. String expression can be a character string or binary data.
  2. integer expression. Any integer type, including bigint. If negative, null is returned. If 0, an empty string is returned.

  SELECT REPLICATE('a', -1)  -- Returns NULL
  
  SELECT REPLICATE('a', 0)  -- Returns ''
  
  SELECT REPLICATE('a', 5)  -- Returns 'aaaaa'
  
  SELECT REPLICATE('Abc', 3) -- Returns 'AbcAbcAbc'
  

  Note: If string expression is not of type varchar(max) or nvarchar(max), the return value will not exceed 8000 chars. Replicate will stop before adding the string that will cause the return value to exceed that limit:

  SELECT LEN(REPLICATE('a b c d e f g h i j k l', 350)) -- Returns 7981
  
  SELECT LEN(REPLICATE(cast('a b c d e f g h i j k l' as varchar(max)), 350)) -- Returns 8050
  

  created Jul 27 '16 at 10:38

  
• 2

  Reverse

  edit move example delete share

  2

  Returns a string value in reversed order.

  Parameters:

  1. string expression. Any string or binary data that can be implicitly converted to varchar.

  Select REVERSE('Sql Server') -- Returns 'revreS lqS'
  

  edited Jul 27 '16 at 16:15

  

  
• 2

  Right

  edit move example delete share

  2

  Returns a sub string that is the right most part of the string, with the specified max length.

  Parameters:

  1. character expression. The character expression can be of any data type that can be implicitly converted to varchar or nvarchar, except for text or ntext
  2. max length. An integer number between 0 and bigint max value (9,223,372,036,854,775,807). If the max length parameter is negative, an error will be raised.

  SELECT RIGHT('This is my string', 6) -- returns 'string'
  

  If the max length is more then the number of characters in the string, the entier string is returned.

  SELECT RIGHT('This is my string', 50) -- returns 'This is my string'
  

  edited Nov 19 '16 at 19:39

  

  
• 2

  RTrim

  edit move example delete share

  2

  Returns a character expression (varchar or nvarchar) after removing all trailing white spaces, i.e., spaces from the right end of the string up until the first non-white space character to the left.

  Parameters:

  1. character expression. Any expression of character or binary data that can be implicitly converted to varcher, except text, ntext and image.

  SELECT RTRIM('This is my string     ') -- Returns 'This is my string'
  

  edited Aug 4 '16 at 4:17

  

  
• 2

  Str

  edit move example delete share

  2

  Returns character data (varchar) converted from numeric data.

  Parameters:

  1. float expression. An approximate numeric data type with a decimal point.
  2. length. optional. The total length of the string expression that would return, including digits, decimal point and leading spaces (if needed). The default value is 10.
  3. decimal. optional. The number of digits to the right of the decimal point. If higher then 16, the result would be truncated to sixteen places to the right of the decimal point.

  SELECT STR(1.2) -- Returns '         1'
  
  SELECT STR(1.2, 3) -- Returns '  1'
  
  SELECT STR(1.2, 3, 2) -- Returns '1.2'
  
  SELECT STR(1.2, 5, 2) -- Returns ' 1.20'
  
  SELECT STR(1.2, 5, 5) -- Returns '1.200'
  
  SELECT STR(1, 5, 2) -- Returns ' 1.00'
  
  SELECT STR(1) -- Returns '         1'
  

  created Jul 29 '16 at 0:56

  
• 2

  Unicode

  edit move example delete share

  2

  Returns the integer value representing the Unicode value of the first character of the input expression.

  Parameters:

  1. Unicode character expression. Any valid nchar or nvarchar expression.
     Note: char and varchar values, even if set to a nvarchar variable, will cause the unicode function to return the wrong result.

  SELECT UNICODE(N'Ɛ') -- Returns 400
  
  DECLARE @Unicode nvarchar(11) = N'Ɛ is a char'
  SELECT UNICODE(@Unicode) -- Returns 400
  
  SET @Unicode = 'Ɛ is a char'
  SELECT UNICODE(@Unicode) -- Returns 63 (!!!)
  

  created Jul 26 '16 at 21:19

  
• 2

  Upper

  edit move example delete share

  2

  Returns a character expression (varchar or nvarchar) after converting all lowercase characters to uppercase.

  Parameters:

  1. Character expression. Any expression of character or binary data that can be implicitly converted to varchar.

  SELECT UPPER('This IS my STRING') -- Returns 'THIS IS MY STRING'
  
  DECLARE @String nchar(17) = N'This IS my STRING';
  SELECT UPPER(@String) -- Returns 'THIS IS MY STRING'
  

  created Jul 26 '16 at 21:19