Top 30 SQL String Functions with Usage and Syntax

Introduction

Think of SQL string functions as a Swiss Army knife for working with text in databases. They’re the go-to tools to slice, dice, clean up, or transform text data. Whether you’re a developer trying to make sense of messy user input or an analyst preparing data for a report, these functions have your back. But what exactly are SQL String Functions? Need to join two pieces of text together? There’s a function for that. Want to pull out just a part of a long string? Yep, covered. Intriguing right?

How about turning everything to uppercase or finding a specific word in a sentence? SQL string functions can handle all that and more. They’re the unsung heroes of data wrangling, making our lives easier when handling text in databases. In this article, we will talk about all about SQL String functions.

Overview

• Explore SQL string functions for text manipulation and data transformation in databases.
• Learn essential SQL string functions from concatenation to substring extraction.
• Understand best practices for optimizing performance and handling data types in SQL string operations.
• Discover 30 powerful SQL string functions with syntax and practical examples.
• Master SQL string functions for efficient data cleaning, analysis, and database reporting.

Importance of String Functions

The importance of string functions in SQL cannot be overstated. They play a crucial role in:

• Data Cleaning: Remove unwanted characters, standardize formats, and correct inconsistencies in text data.
• Data Transformation: Converting data from one format to another, such as changing date formats or extracting specific string parts.
• Text Analysis: Performing operations like counting occurrences of substrings or finding specific patterns within text.
• Reporting: Formatting text data for better readability and presentation in reports.
• Search Functionality: Implementing application search features by matching patterns or keywords in text fields.

Best Practices for Using String Functions

• Performance: Some string functions can be computationally expensive, especially on large datasets. Use them judiciously and consider indexing strategies when appropriate.
• Data Types: Ensure you’re using the correct data types for your string columns (e.g., VARCHAR vs. NVARCHAR for Unicode support).
• Null Handling: Be aware of how each function handles NULL values and use COALESCE or ISNULL when necessary.
• Collation: Understand the impact of database and column collations on string operations, especially in multi-language environments.
• Testing: Thoroughly test your string manipulations, especially with edge cases and special characters.

Let’s explore a comprehensive list of string functions, their usage, syntax, and examples.

String Functions in SQL

Here are 30 String functions in SQL:

1. ASCII(American Standard Code for Information Interchange)

Usage: Returns the ASCII value for the specific character.
Syntax: ASCII(character)

SELECT ASCII('A') AS ascii_value;

Result: 65

2. CHAR(Character)

Usage: Returns the character based on the ASCII code.
Syntax: CHAR(ascii_code)

SELECT CHAR(65) AS character;

Result: 'A'

3. CHARINDEX Function

Usage: Returns the position of a substring.
Syntax: CHARINDEX(substring, string [, start_position])

SELECT CHARINDEX('World', 'Hello World') AS position;

Result: 7

4. CONCAT Function

Usage: Add two or more strings together.
Syntax: `CONCAT(string1, string2, …)`

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

Result: 'Hello World'

5. Concat With + Function

Usage: Add two or more strings together using the + operator.

Syntax: `string1 + string2 + …`

SELECT 'Hello' + ' ' + 'World' AS greeting;

Result: 'Hello World'

6. CONCAT_WS Function

Usage: Add two or more strings together with a separator.
Syntax: `CONCAT_WS(separator, string1, string2, …)`

SELECT CONCAT_WS(', ', 'John', 'Doe', 'Smith') AS full_name;

Result: 'John, Doe, Smith'

7. DATALENGTH Function

Usage: Returns the number of bytes used to represent an expression.
Syntax: `DATALENGTH(expression)`

SELECT DATALENGTH('Hello') AS byte_length;

Result: 5

8. DIFFERENCE Function

Usage: Compares two SOUNDEX values and returns an integer value.
Syntax: `DIFFERENCE(string1, string2)`

SELECT DIFFERENCE('Smith', 'Smyth') AS sound_difference;

Result: 4 (values range from 0 to 4, with 4 being the most similar)

9. FORMAT Function

Usage: Formats a value with the specified format.
Syntax: `FORMAT(value, format)`

SELECT FORMAT(123456.789, 'N2') AS formatted_number;

Result: '123,456.79'

10. LEFT Function

Usage: Extracts a number of characters from a string (starting from left).
Syntax: `LEFT(string, number_of_chars)`

SELECT LEFT('Hello World', 5) AS left_chars;

Result: 'Hello'

11. LEN

Usage: Returns the length of a string.
Syntax: `LEN(string)`

SELECT LEN('Hello World') AS string_length;

Result: 11

12. LOWER

Usage: Convert a string to lower-case.
Syntax: `LOWER(string)`

SELECT LOWER('HELLO WORLD') AS lowercase_text;

Result: 'hello world'

13. LTRIM

Usage: Removes leading spaces from a string.
Syntax: `LTRIM(string)`

SELECT LTRIM('   Hello World') AS trimmed_text;

Result: 'Hello World'

14. NCHAR

Usage: Returns the Unicode character based on the number code.
Syntax: `NCHAR(unicode_code)`

SELECT NCHAR(65) AS unicode_char;

Result: 'A'

15. PATINDEX

Usage: Returns the position of a pattern in a string.
Syntax: `PATINDEX(‘%pattern%’, string)`

SELECT PATINDEX('%World%', 'Hello World') AS pattern_position;

Result: 7

16. QUOTENAME

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

SELECT QUOTENAME('My Table') AS quoted_name;

Result: '[My Table]'

17. REPLACE

Usage: Replaces all occurrences of a substring within a string with a new substring.
Syntax: `REPLACE(string, old_substring, new_substring)`

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

Result: 'Hello SQL'

18. REPLICATE

Usage: Repeats a string a number of times.
Syntax: `REPLICATE(string, number_of_times)`

SELECT REPLICATE('SQL ', 3) AS repeated_text;

Result: 'SQL SQL SQL '

19. REVERSE

Usage: Reverses a string and returns the result.
Syntax: `REVERSE(string)`

SELECT REVERSE('Hello') AS reversed_text;

Result: 'olleH'

20. RIGHT

Usage: Extracts a number of characters from a string (starting from right).
Syntax: `RIGHT(string, number_of_chars)`

SELECT RIGHT('Hello World', 5) AS right_chars;

Result: 'World'

21. RTRIM

Usage: Removes trailing spaces from a string.
Syntax: `RTRIM(string)`

SELECT RTRIM('Hello World   ') AS trimmed_text;

Result: 'Hello World'

22. SOUNDEX

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

SELECT SOUNDEX('Smith'), SOUNDEX('Smyth');

Results: 'S530', 'S530'

23. SPACE

Usage: Returns a string of the specified number of space characters.
Syntax: `SPACE(number_of_spaces)`

SELECT 'Hello' + SPACE(5) + 'World' AS spaced_text;

Result: 'Hello     World'

24. STR

Usage: Returns a number as a string.
Syntax: `STR(number [, length [, decimal_places]])`

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

Result: ' 123.5'

25. STUFF

Usage: Deletes a part of a string and then inserts another part into the string, starting at a specified position.
Syntax: `STUFF(string, start, length, new_string)`

SELECT STUFF('Hello World', 7, 5, 'SQL') AS modified_text;

Result: 'Hello SQL'

26. SUBSTRING

Usage: Extracts some characters from a string.
Syntax: `SUBSTRING(string, start, length)`

SELECT SUBSTRING('Hello World', 7, 5) AS extracted_text;

Result: 'World'

27. TRANSLATE

Usage: The string from the first argument gets a result after the characters specified in the second argument are translated into the characters in the third argument.
Syntax: `TRANSLATE(string, characters_to_replace, replacement_characters)`

SELECT TRANSLATE('2017-08-25', '0123456789', 'abcdefghij') AS translated_text;

Result: 'baih-ai-be'

28. TRIM

Usage: Removes leading and trailing spaces (or other specified characters) from a string.
Syntax: `TRIM([characters FROM] string)`

SELECT TRIM('   Hello World   ') AS trimmed_text;

Result: 'Hello World'

29. UNICODE

Usage: Returns the Unicode value for the first character of the input expression.
Syntax: `UNICODE(character_expression)`

SELECT UNICODE('A') AS unicode_value;

Result: 65

30. UPPER

Usage: Converts a string to upper-case.
Syntax: `UPPER(string)`

SELECT UPPER('hello world') AS uppercase_text;

Result: 'HELLO WORLD'

Conclusion

The functions mentioned in this article can address many tasks related to string manipulation. Note that the actual syntax and availability of functions might differ slightly across different database management systems, such as MySQL or Postgres—it is always a good idea to check your DBMS documentation for details on these.

Frequently Asked Questions

Abhishek Kumar 18 Jul, 2024

Hello, I'm Abhishek, a Data Engineer Trainee at Analytics Vidhya. I'm passionate about data engineering and video games I have experience in Apache Hadoop, AWS, and SQL,and I keep on exploring their intricacies and optimizing data workflows :)