Top 50 SQL String Functions with Usages and Syntax

What is SQL String Function?

SQL string functions are built-in functions that allow manipulation and analysis of string data in SQL databases. They perform operations like extracting substrings, changing case, trimming whitespace, finding string lengths, concatenating strings, and searching for patterns within text. Common SQL string functions include CONCAT(), SUBSTRING(), LENGTH(), UPPER(), LOWER(), TRIM(), REPLACE(), and LIKE. These functions enable powerful text processing capabilities directly within SQL queries, allowing developers to efficiently work with and transform string data as part of database operations without needing external programming. String functions are essential tools for formatting, cleaning, and extracting insights from textual data stored in relational databases.

Top 50 SQL String Functions with Example

ASCII and Character Functions

1. ASCII()

Usage: Returns the ASCII code value of the leftmost character of a character expression.

Syntax: ASCII(character_expression)

SELECT ASCII('A') AS ASCII_Value;

Output: 65

2. CHAR()

Usage: Converts an integer ASCII code to a character.

Syntax: CHAR(integer_expression)

SELECT CHAR(65) AS Character_Value;

Output: A

3. NCHAR()

Usage: Returns the Unicode character with the specified integer code.

Syntax: NCHAR(integer_expression)

SELECT NCHAR(65) AS Unicode_Character;

Output: A

4. UNICODE()

Usage: Returns the integer value of the first character of the input expression.

Syntax: UNICODE(ncharacter_expression)

SELECT UNICODE('A') AS Unicode_Value;

Output: 65

String Length and Manipulation

5. LEN()

Usage: Returns the number of characters of the specified string expression.

Syntax: LEN(string_expression)

SELECT LEN('Hello') AS Length;

Output: 5

6. DATALENGTH()

Usage: Returns the number of bytes used to represent an expression.

Syntax: DATALENGTH(expression)

SELECT DATALENGTH('Hello') AS Data_Length;

Output: 5

7. LEFT()

Usage: Returns the left part of a character string with the specified number of characters.

Syntax: LEFT(character_expression, integer_expression)

SELECT LEFT('Hello', 2) AS Left_Part;

Output: He

8. RIGHT()

Usage: Returns the right part of a character string with the specified number of characters.

Syntax: RIGHT(character_expression, integer_expression)

SELECT RIGHT('Hello', 2) AS Right_Part;

Output: lo

9. SUBSTRING()

Usage: Returns part of a character, binary, text, or image expression.

Syntax: SUBSTRING(expression, start, length)

SELECT SUBSTRING('Hello', 2, 3) AS Substring_Part;

Output: ell

10. LTRIM()

Usage: Returns a character expression after removing leading blanks.

Syntax: LTRIM(character_expression)

SELECT LTRIM(' Hello') AS Trimmed_Left;

Output: Hello

11. RTRIM()

Usage: Returns a character string after truncating all trailing blanks.

Syntax: RTRIM(character_expression)

SELECT RTRIM('Hello ') AS Trimmed_Right;

Output: Hello

12. TRIM()

Usage: Removes leading and trailing blanks (or other specified characters) from a string.

Syntax: TRIM([characters FROM] string)

SELECT TRIM(' Hello ') AS Trimmed;

Output: Hello

Case Conversion

13. LOWER()

Usage: Returns a character expression after converting uppercase character data to lowercase.

Syntax: LOWER(character_expression)

SELECT LOWER('HELLO') AS Lowercase;

Output: hello

14. UPPER()

Usage: Returns a character expression with lowercase character data converted to uppercase.

Syntax: UPPER(character_expression)

SELECT UPPER('hello') AS Uppercase;

Output: HELLO

String Concatenation

15. CONCAT()

Usage: Returns a string that is the result of concatenating two or more string values.

Syntax: CONCAT(string1, string2 [, stringN])

SELECT CONCAT('Hello', ' ', 'World') AS Concatenated_String;

Output: Hello World

16. CONCAT_WS()

Usage: Returns a string resulting from the concatenation of two or more string values with a separator.

Syntax: CONCAT_WS(separator, string1, string2 [, stringN])

SELECT CONCAT_WS('-', '2024', '07', '22') AS Concatenated_With_Separator;

Output: 2024-07-22

17. STRING_AGG()

Usage: Concatenates the values of string expressions and places separator values between them.

Syntax: STRING_AGG(expression, separator) [WITHIN GROUP (ORDER BY expression)]

SELECT STRING_AGG(name, ', ') AS Aggregated_Names FROM (VALUES ('Alice'), ('Bob'), ('Charlie')) AS Names(name);

Output: Alice, Bob, Charlie

String Searching

18. CHARINDEX()

Usage: Searches for a substring in a string and returns its starting position.

Syntax: CHARINDEX(substring, string [, start_location])

SELECT CHARINDEX('l', 'Hello') AS Position;

Output: 3

19. PATINDEX()

Usage: Returns the starting position of the first occurrence of a pattern in a specified expression.

Syntax: PATINDEX('%pattern%', expression)

SELECT PATINDEX('%ell%', 'Hello') AS Pattern_Position;

Output: 2

20. SOUNDEX()

Usage: Returns a four-character code to evaluate the similarity of two strings.

Syntax: SOUNDEX(character_expression)

SELECT SOUNDEX('Hello') AS Soundex_Code;

Output: H400

String Replacement and Modification

21. REPLACE()

Usage: Replaces all occurrences of a specified string value with another string value.

Syntax: REPLACE(string_expression, string_pattern, string_replacement)

SELECT REPLACE('Hello World', 'World', 'SQL') AS Replaced_String;

Output: Hello SQL

22. STUFF()

Usage: Deletes a specified length of characters and inserts another set of characters at a specified starting point.

Syntax: STUFF(character_expression, start, length, replaceWith_expression)

SELECT STUFF('Hello World', 6, 5, 'SQL') AS Stuffed_String;

Output: Hello SQL

23. TRANSLATE()

Usage: Returns the string from the first argument after the characters specified in the second argument are translated into the characters specified in the third argument.

Syntax: TRANSLATE(inputString, characters, translations)

SELECT TRANSLATE('Hello', 'el', 'ip') AS Translated_String;

Output: Hippo

24. REVERSE()

Usage: Returns the reverse order of a string value.

Syntax: REVERSE(string_expression)

SELECT REVERSE('Hello') AS Reversed_String;

Output: olleH

25. SPACE()

Usage: Returns a string of repeated spaces.

Syntax: SPACE(integer_expression)

SELECT SPACE(5) + 'Hello' AS Spaced_String;

Output: Hello

26. REPLICATE()

Usage: Repeats a string value a specified number of times.

Syntax: REPLICATE(string_expression, integer_expression)

SELECT REPLICATE('Hello', 3) AS Replicated_String;

Output: HelloHelloHello

String Formatting

27. FORMAT()

Usage: Returns a value formatted with the specified format and optional culture.

Syntax: FORMAT(value, format [, culture])

SELECT FORMAT(1234.5678, 'N2') AS Formatted_Value;

Output: 1,234.57

28. STR()

Usage: Returns character data converted from numeric data.

Syntax: STR(float_expression [, length [, decimal]])

SELECT STR(123.45, 6, 1) AS String_Value;

Output: 123.5

29. QUOTENAME()

Usage: Returns a Unicode string with delimiters added to make the string a valid SQL Server delimited identifier.

Syntax: QUOTENAME('character_string' [, 'quote_character'])

SELECT QUOTENAME('TableName') AS Quoted_Name;

Output: [TableName]

String Parsing

30. PARSENAME()

Usage: Returns the specified part of an object name.

Syntax: PARSENAME('object_name', object_piece)

SELECT PARSENAME('Database.Schema.Table', 1) AS Table_Name;

Output: Table

31. STRING_SPLIT()

Usage: Splits a string into rows of substrings based on a specified separator.

Syntax: STRING_SPLIT(string, separator)

SELECT value AS Split_Value FROM STRING_SPLIT('Hello,World,SQL', ',');

Output: Hello World SQL

32. STRING_ESCAPE()

Usage: Escapes special characters in texts and returns text with escaped characters.

Syntax: STRING_ESCAPE(text, type)

SELECT STRING_ESCAPE('{"name": "John"}', 'json') AS Escaped_String;

Output: {\”name\”: \”John\”}

XML String Functions

33. XML_QUERY()

Usage: Returns an XML value from an XQuery expression.

Syntax: XML_QUERY('XQuery_expression')

SELECT XML_QUERY('<root><element>value</element></root>') AS XML_Result;

Output: <root><element>value</element></root>

34. XML_VALUE()

Usage: Returns a scalar value from an XQuery expression.

Syntax: XML_VALUE('XQuery_expression', 'data_type')

SELECT XML_VALUE('<root><element>value</element></root>', 'VARCHAR(100)') AS Scalar_Value;

Output: value

JSON String Functions

35. JSON_VALUE()

Usage: Extracts a scalar value from a JSON string.

Syntax: JSON_VALUE(expression, path)

SELECT JSON_VALUE('{"name": "John"}', '$.name') AS Name;

Output: John

36. JSON_QUERY()

Usage: Extracts an object or array from a JSON string.

Syntax: JSON_QUERY(expression [, path])

SELECT JSON_QUERY('{"name": "John", "info": {"age": 30}}', '$.info') AS Info;

Output: {“age”: 30}

37. JSON_MODIFY()

Usage: Updates the value of a property in a JSON string and returns the updated JSON string.

Syntax: JSON_MODIFY(expression, path, new_value)

SELECT JSON_MODIFY('{"name": "John"}', '$.name', 'Jane') AS Modified_JSON;

Output: {“name”: “Jane”}

Collation and Comparison

38. COLLATE

Usage: Specifies the collation for an expression.

Syntax: expression COLLATE collation_name

SELECT 'Hello' COLLATE Latin1_General_BIN AS Collated_String;

Output: Hello

39. DIFFERENCE()

Usage: Returns an integer that indicates the difference between the SOUNDEX values of two character expressions.

Syntax: DIFFERENCE(character_expression1, character_expression2)

SELECT DIFFERENCE('Hello', 'Hullo') AS Difference_Value;

Output: 4

Encryption and Hashing

40. HASHBYTES()

Usage: Returns the hash value of the input using the specified algorithm.

Syntax: HASHBYTES(algorithm, input)

SELECT HASHBYTES('SHA2_256', 'Hello') AS Hash_Value;

Output: 0x185F8DB32271FE25F561A6FC938B2E264306EC304EDA518007D1764826381969

41. ENCRYPTBYPASSPHRASE()

Usage: Encrypts data using a passphrase.

Syntax: ENCRYPTBYPASSPHRASE(passphrase, cleartext [, add_authenticator])

SELECT ENCRYPTBYPASSPHRASE('myPassphrase', 'Hello') AS Encrypted_Value;

Output: 0x01000000…

42. DECRYPTBYPASSPHRASE()

Usage: Decrypts data that was encrypted using ENCRYPTBYPASSPHRASE.

Syntax: DECRYPTBYPASSPHRASE(passphrase, ciphertext [, add_authenticator])

SELECT CONVERT(VARCHAR, DECRYPTBYPASSPHRASE

Output: Hello World

Miscellaneous String Functions

43. COMPRESS()

Usage: Compresses the input string.
Syntax: COMPRESS(string)

44. DECOMPRESS()

Usage: Decompresses the input string that was compressed using COMPRESS.
Syntax: DECOMPRESS(binary_string)

45. STRING_SPLIT()

Usage: Splits a string into rows of substrings based on a specified separator.
Syntax: STRING_SPLIT(string, separator)

46. TRANSLATE()

Usage: Returns the string from the first argument after the characters specified in the second argument are translated into the characters specified in the third argument.
Syntax: TRANSLATE(inputString, characters, translations)

47. SOUNDEX()

Usage: Returns a four-character code to evaluate the similarity of two strings.
Syntax: SOUNDEX(character_expression)

48. DIFFERENCE()

Usage: Returns an integer that indicates the difference between the SOUNDEX values of two character expressions.
Syntax: DIFFERENCE(character_expression1, character_expression2)

49. FORMAT()

Usage: Returns a value formatted with the specified format and optional culture.
Syntax: FORMAT(value, format [, culture])

50. QUOTENAME()

Usage: Returns a Unicode string with delimiters added to make the string a valid SQL Server delimited identifier.
Syntax: QUOTENAME('character_string' [, 'quote_character'])

Importance of SQL String Function

• Data manipulation: SQL string functions allow for efficient manipulation and transformation of text data directly within database queries.
• Data cleaning: They enable cleaning and standardising text data by removing unwanted characters, changing case, or trimming whitespace.
• Data extraction: String functions can extract specific portions or patterns from longer text strings.
• Data concatenation: They allow combining multiple text fields or adding prefixes/suffixes to create new text values.
• Pattern matching: Functions like LIKE and REGEXP enable searching for specific patterns within text data.
• Formatting: String functions help format text data for consistent presentation or to meet specific requirements.
• Performance: Using built-in string functions is often more efficient than processing text data in application code.
• Flexibility: They provide a wide range of text manipulation capabilities without needing external programming languages.
• Data analysis: String functions facilitate text-based analysis and categorisation directly in SQL queries.
• Standardisation: They help enforce data standards by allowing text transformations during data input or retrieval.
• Cross-database compatibility: Many string functions are supported across different database systems, enhancing query portability.