String Function in SQL Server

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

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

CHAR

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

NCHAR

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

CHARINDEX

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

CONCAT

  • 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

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

RIGHT

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

LEN

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

DATALENGTH

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

LOWER

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

UPPER

  • 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

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

RTRIM

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

PATINDEX

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

REPLACE

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

STUFF

  • 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

  • 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

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

SUBSTRING

  • 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: -