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