IT2EDU Empowering Education Through Technology
SQL has several string functions that are used to manipulate and process character string data. Here are some of the most commonly used string functions in SQL:
String Functions in SQL:
CONCAT()
Syntax: CONCAT(string1, string2, …)
Definition: This function is used to concatenate two or more strings into a single string.
LENGTH()
Syntax: LENGTH(string)
Definition: This function returns the length of the specified string.
UPPER()
Syntax: UPPER(string)
Definition: This function returns the specified string converted to uppercase.
LOWER()
Syntax: LOWER(string)
Definition: This function returns the specified string converted to lowercase.
SUBSTRING()Syntax: SUBSTRING(string, start_position, length)
Definition: This function returns a substring of the specified string, starting from the specified position and with the specified length.
REPLACE()Syntax: REPLACE(string, search_string, replace_string)
Definition: This function replaces all occurrences of the search string in the specified string with the replace string.
TRIM()Syntax: TRIM([LEADING | TRAILING | BOTH] [characters FROM] string)
Definition: This function removes the specified characters from the beginning, end, or both ends of the specified string.
LTRIM()Syntax: LTRIM(string)
Definition: This function removes any leading spaces from the specified string.
RTRIM()Syntax: RTRIM(string)Definition: This function removes any trailing spaces from the specified string.
CHARINDEX()Syntax: CHARINDEX(search_string, string, [start_position])
Definition: This function returns the starting position of the specified search string in the specified string. If the start position is specified, the search begins at that position.
LEFT()Syntax: LEFT(string, length)
Definition: This function returns a specified number of characters from the beginning of the specified string.
RIGHT()Syntax: RIGHT(string, length)
Definition: This function returns a specified number of characters from the end of the specified string.
REPLICATE()Syntax: REPLICATE(string, number_of_times)
Definition: This function returns the specified string repeated a specified number of times.
STUFF()Syntax: STUFF(string, start_position, length, replace_string)
Definition: This function deletes a specified length of characters from the specified string, starting at the specified position, and then inserts the specified replacement string.
ASCII()Syntax: ASCII(string)
Definition: This function returns the ASCII code value of the first character in the specified string.
CHAR()Syntax: CHAR(code)
Definition: This function returns the character that corresponds to the specified ASCII code value.
DIFFERENCE()Syntax: DIFFERENCE(string1, string2)
Definition: This function compares two strings and returns an integer value that indicates how similar they are.
FORMAT()Syntax: FORMAT(expression, format)
Definition: This function formats the specified expression using the specified format.
PATINDEX()Syntax: PATINDEX(pattern, string)
Definition: This function returns the starting position of the first occurrence of a specified pattern in the specified string.
QUOTENAME()Syntax: QUOTENAME(string, [quote_character])
Definition: This function returns a Unicode string with the specified string wrapped in brackets, and with any existing brackets doubled.
REVERSE()Syntax: REVERSE(string)
Definition: This function reverses the order of the characters in the specified string.
SOUNDEX()Syntax: SOUNDEX(string)
Definition: This function returns a phonetic representation of the specified string.
SPACE()Syntax: SPACE(number_of_spaces)
Definition: This function returns a string with the specified number of spaces.
STR()Syntax: STR(number, length, decimal_places)
Definition: This function converts a number to a string with the specified length and number of decimal places.
UNICODE()Syntax: UNICODE(character)
Definition: This function returns the Unicode code point value of the specified character.
UPPER()Syntax: UPPER(string)
Definition: This function returns the specified string converted to uppercase.
CONCAT_WS()Syntax: CONCAT_WS(separator, string1, string2, …)
Definition: String functions in SQL CONCAT_WS is a function, This function concatenates two or more strings into a single string, separated by the specified separator.
FORMATMESSAGE()Syntax: FORMATMESSAGE(message, parameter1, parameter2, …)
Definition: String functions in SQL, FORMATMESSAGE is a function, and this function returns a formatted message string, with the specified placeholders replaced by the specified values.
INITCAP()Syntax: INITCAP(string)
Definition: This function returns the specified string with the first character of each word capitalized, and all other characters in lowercase.
LEN()Syntax: LEN(string)
Definition: String functions in SQL, LEN is an important function. This function returns the length of the specified string.
LOCATE()Syntax: LOCATE(substring, string, [start_position])
Definition: This function returns the starting position of the specified substring in the specified string. If the start position is specified, the search begins at that position.
NCHAR()Syntax: NCHAR(code)
Definition: This function returns the Unicode character that corresponds to the specified Unicode code point value.
PATINDEX()Syntax: PATINDEX(pattern, string)
Definition: This function returns the starting position of the first occurrence of a specified pattern in the specified string.
QUOTENAME()Syntax: QUOTENAME(string, [quote_character])
Definition: QUOTENAME is is string functions in sql, This function returns a Unicode string with the specified string wrapped in brackets, and with any existing brackets doubled.
REPLICATE()Syntax: REPLICATE(string, number_of_times)
Definition:In string functions in sql REPLICATE is another function, This function returns the specified string repeated a specified number of times.
REPLACE()
Syntax: REPLACE(string, search_string, replace_string)
Definition: In string functions in sql REPLACE is another function, This function replaces all occurrences of the search string in the specified string with the replace string.
SPACE()Syntax: SPACE(number_of_spaces)
Definition: This function returns a string with the specified number of spaces.
SUBSTRING()
Syntax: SUBSTRING(string, start_position, length)
Definition: In string functions in Sql SUBSTRING is another function, it returns a substring of the specified string, starting from the specified position and with the specified length.
For more information or functions visit the official website.
Also look into the interview stuff related to Java, SQL and Power BI by visiting this blog.
If you are interested to share the content or any article submit here.
