String Function in SQL Server

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

Get Vaishali’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

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