String Function in SQL Server

Vaishali Goilkar
2 min readMay 21, 2020

In this article, we learn about string function in SQL Server.

SQL Server string functions process on an input string and return a string or numeric value.


  • ASCII stands for American Standard Code for Information Interchange.
  • It returns the ASCII code value for the specific character.
  • Syntax: ASCII ( input_string )


  • CHAR returns the character based on the ASCII code. It is exactly opposite to the ASCII code.
  • Syntax: CHAR ( integer_expression )


  • NCHAR returns the Unicode character based on the number code.
  • Syntax: NCHAR ( integer_expression )


  • CHARINDEX returns the position of a substring in a string.
  • Syntax: CHARINDEX(substring, string [, start_location])


  • CONCAT adds two or more strings into one string. It requires at least two input strings.
  • If we pass one input string, then it shows an error.
  • If we pass the non-character string, then the function converts those values into strings before concatenating.
  • Syntax: CONCAT ( input_string1, input_string2 [, input_stringN ] );


  • LEFT extracts several characters from a character string starting from the left.
  • Syntax: LEFT ( input_string , number_of_characters )


  • RIGHT extracts several characters from a string starting from the right.
  • Syntax: RIGHT ( input_string , number_of_characters )


  • LEN returns a length of a character string excluding trailing blanks.
  • Syntax: LEN(input_string)


  • DATALENGTH returns the number of bytes used to represent an expression.
  • The DATALENGTH() function counts trailing blanks.
  • Syntax: DATALENGTH ( expression )


  • LOWER convert a string to lowercase.
  • Syntax: LOWER( string )


  • UPPER converts a string to uppercase.
  • If the input string is NULL, the UPPER() returns NULL, otherwise, it returns a new string with all letters converted to uppercase.
  • Some database systems provide an additional function named UCASE which is the same as the UPPER() function.
  • Syntax: UPPER(string);


  • LTRIM removes leading spaces from the left-hand side of a string.
  • Syntax: LTRIM(input_string)


  • RTRIM returns a new string from a specified string after removing all trailing blanks.
  • Syntax: RTRIM(input_string)


  • PATINDEX returns the starting position of the first occurrence of a pattern in a string.
  • Syntax: PATINDEX ( ‘%pattern%’ , input_string )


  • Replace all occurrences of a substring, within a string, with a new substring.
  • Syntax: REPLACE(input_string, substring, new_substring);


  • STUFF delete a part of a string and then insert another substring into the string starting at a specified position.
  • Syntax:

STUFF ( input_string , start_position , length , replace_with_substring )


  • SPACE returns a string of repeated spaces.
  • Syntax: SPACE(count);
  • The count is a positive integer that specifies the number of spaces. It count is negative, the function will return NULL.


  • STR returns character data converted from numeric data.
  • Syntax: STR( number [, length [, decimal_places ] ] )


  • Extract a substring within a string starting from a specified location with a specified length.
  • Syntax: SUBSTRING(input_string, start, length);

If you are new to database learning — SQL Server recommended is the following must-watch the video: -