SQL | String functions

SQL String Functions are powerful tools that allow us to manipulate, format, and extract specific parts of text data in our database. These functions are essential for tasks like cleaning up data, comparing strings, and combining text fields. Whether we're working with names, addresses, or any form of textual data, mastering SQL string functions is crucial for efficient data handling and analysis.

Common SQL String Functions

String functions are used to perform an operation on input string and return an output string. Below are some of the most commonly used SQL string functions:

1. CONCAT(): Concatenate Strings

The CONCAT() function is used to concatenate (combine) two or more strings into one string. It is useful when we want to merge fields like first and last names into a full name.

Query:

SELECT CONCAT('John', ' ', 'Doe') AS FullName;

Output:

John Doe

2. CHAR_LENGTH() / CHARACTER_LENGTH(): Find String Length

The CHAR_LENGTH() or LENGTH() function returns the length of a string in characters. It’s essential for validating or manipulating text data, especially when you need to know how many characters a string contains.

Query:

SELECT CHAR_LENGTH('Hello') AS StringLength;

Output:

5

3. UPPER() and LOWER(): Convert Text Case

These functions convert the text to uppercase or lowercase, respectively. They are useful for normalizing the case of text in a database.

Query:

SELECT UPPER('hello') AS UpperCase;
SELECT LOWER('HELLO') AS LowerCase;

Output:

HELLO
hello

4. LENGTH(): Length of String in Bytes

LENGTH() returns the length of a string in bytes. This can be useful for working with multi-byte character sets.

Query:

SELECT LENGTH('Hello') AS LengthInBytes;

Output:

5

5. REPLACE(): Replace Substring in String

The REPLACE() function replaces occurrences of a substring within a string with another substring. This is useful for cleaning up data, such as replacing invalid characters or formatting errors.

Query:

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

Output:

Hello SQL

6. SUBSTRING() / SUBSTR(): Extract Part of a String

The SUBSTRING() (or SUBSTR()) function is used to extract a substring from a string, starting from a specified position. It is especially useful when we need to extract a specific part of a string, like extracting the domain from an email address.

Query:

SELECT SUBSTRING('Hello World', 1, 5) AS SubStringExample;

Output:

Hello

7. LEFT() and RIGHT(): Extract Substring from Left or Right

The LEFT() and RIGHT() functions allow you to extract a specified number of characters from the left or right side of a string, respectively. It is used for truncating strings for display.

Query:

SELECT LEFT('Hello World', 5) AS LeftString;
SELECT RIGHT('Hello World', 5) AS RightString;

Output:

Hello    
World

8. INSTR(): Find Position of Substring

The INSTR() function is used to find the position of the first occurrence of a substring within a string. It returns the position (1-based index) of the substring. If the substring is not found, it returns 0. This function is particularly useful for locating specific characters or substrings in text data.

Query:

SELECT INSTR('Hello World', 'World') AS SubstringPosition;

Output:

7

9. TRIM(): Remove Leading and Trailing Spaces

The TRIM() function removes leading and trailing spaces (or other specified characters) from a string. By default, it trims spaces but can also remove specific characters using TRIM(character FROM string). This is helpful for cleaning text data, such as user inputs or database records.

Query:

SELECT TRIM(' ' FROM '  Hello World  ') AS TrimmedString;

Output:

Hello World

10. REVERSE(): Reverse the String

The REVERSE() function reverses the characters in a string. It’s useful in situations where we need to process data backward, such as for password validation or certain pattern matching.

Query:

SELECT REVERSE('Hello') AS ReversedString;

Output:

olleH

Other String Functions

In SQL, beyond the basic string functions, there are several advanced string functions that can help you manipulate and process string data more effectively. These functions provide powerful tools for working with text, whether you're cleaning data, formatting outputs, or comparing strings. These are the some additional SQL Functions.

11. ASCII():Get the ASCII Value of a Character

The ASCII() function returns the ASCII value of a single character. This is helpful when we need to find the numeric code corresponding to a character, often used in encoding and decoding text.

Syntax:

SELECT ascii('t');

Output:

 116

12. CONCAT_WS(): Concatenate Strings with a Separator

CONCAT_WS() stands for "Concatenate With Separator." It allows us to join multiple strings with a specific separator between them. This is ideal when we need to merge columns like first name and last name with a custom separator.

Syntax:

SELECT CONCAT_WS('_', 'geeks', 'for', 'geeks');

Output:

geeks_for_geeks

13. FIND_IN_SET(): Find Position of a Value in a Comma-Separated List

The FIND_IN_SET() function returns the position of a value within a comma-separated list. This is especially useful for finding out where an element exists in a string of values (e.g., tags, categories).

Syntax:

SELECT FIND_IN_SET('b', 'a, b, c, d, e, f');

Output:

 2

14. FORMAT(): Format Numbers for Readable Output

The FORMAT() function is used to format a number as a string in a specific way, often with commas for thousands or with a specific number of decimal places. It's handy when you need to display numbers in a user-friendly format.

Syntax:

SELECT FORMAT(0.981 * 100, 'N2') + '%' AS PercentageOutput;

Output:

‘98.10%’

15. INSTR(): Find the Position of a Substring

The INSTR() function returns the position of the first occurrence of a substring within a string. If the substring is not found, it returns 0. It's useful for finding where specific text appears in a larger string.

Syntax:

SELECT INSTR('geeks for geeks', 'e');

Output:

2 

16. LCASE(): Convert String to Lowercase

The LCASE() function converts all characters in a string to lowercase. It helps standardize text data, especially when comparing strings in a case-insensitive way.

Syntax:

SELECT LCASE ("GeeksFor Geeks To Learn");

Output:

geeksforgeeks to learn

17. LOCATE(): Find the nth Position of a Substring

LOCATE() allows you to find the nth occurrence of a substring in a string. This is especially useful when you need to locate a specific substring based on its position.

Syntax:

SELECT LOCATE('for', 'geeksforgeeks', 1);

Output:

 6

18. LPAD(): Pad the Left Side of a String

LPAD() is used to pad a string to a certain length by adding characters to the left side of the original string. It's useful when you need to format data to a fixed length.

Syntax:

SELECT LPAD('geeks', 8, '0');

Output:

000geeks

19. MID(): Extract a Substring from the Middle

MID() extracts a substring starting from a given position in a string and for a specified length. It's useful when you want to extract a specific portion of a string.

Syntax:

SELECT Mid ("geeksforgeeks", 6, 2);

Output:

for

20. POSITION(): Find the Position of a Character in a String

The POSITION() function finds the position of the first occurrence of a specified character in a string.

Syntax:

SELECT POSITION('e' IN 'geeksforgeeks');

Output:

2

21. REPEAT(): Repeat a String Multiple Times

The REPEAT() function repeats a string a specified number of times. It's useful when you need to duplicate a string or pattern for certain operations.

Syntax:

SELECT REPEAT('geeks', 2);

Output:

geeksgeeks

22. REPLACE(): Replace a Substring in a String

REPLACE() is used to replace all occurrences of a substring with another substring. It's useful for replacing or cleaning up certain text in your data.

Syntax:

REPLACE('123geeks123', '123');

Output:

geeks

23. RPAD(): Pad the Right Side of a String

RPAD() pads the right side of a string with specified characters to a fixed length. This is often used to format text or numbers to a desired size.

Syntax:

RPAD('geeks', 8, '0');

Output:

 ‘geeks000’

24. RTRIM(): Remove Trailing Characters

RTRIM() removes trailing characters from the right side of a string. By default, it removes spaces, but you can specify other characters as well.

Syntax:

RTRIM('geeksxyxzyyy', 'xyz');

Output:

 ‘geeks’

25. SPACE(): Generate a String of Spaces

The SPACE() function generates a string consisting of a specified number of spaces. This is useful when you need to format output or create padding in your queries.

Syntax:

SELECT SPACE(7);

Output:

 ‘       ‘

26. STRCMP(): Compare Two Strings

STRCMP() compares two strings and returns an integer value based on their lexicographical comparison. This is useful for sorting or checking equality between two strings.

Syntax:

SELECT STRCMP('google.com', 'geeksforgeeks.com');

Output:

 -1

Summary of String Functions

Below is a table summarizing these functions, their purposes, and examples.

Conclusion

SQL String Functions are powerful tools for manipulating and analyzing string data in databases. Whether we need to concatenate, extract, compare, or modify strings, these functions provide the flexibility to handle a wide variety of string-related tasks. Understanding and applying these functions can make our SQL queries more efficient and help us manipulate data exactly as we need.