String Functions in SQL

SQL functions known as "string functions" enable the manipulation and modification of string data that is stored in a database. For tasks including concatenation, substring extraction, case conversion, length computation, whitespace removal, and pattern matching, SQL offers a collection of built-in string methods. These SQL functions can be used to change and format data in useful ways, and they can be applied to columns or literal strings. CONCAT(), SUBSTRING(), UPPER(), LOWER(), LENGTH(), TRIM(), and REPLACE() are a few typical SQL string operations.

String functions

CONCAT()

Concatenates two or more strings together.

Example:

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

Output: 'Hello World'

SUBSTRING()

Returns a portion of a string.

Example:

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

Output: 'Hello'

UPPER()

Converts a string to uppercase.

Example:

SELECT UPPER('hello world');

Output: 'HELLO WORLD'

LOWER()

Converts a string to lowercase.

Example:

SELECT LOWER('HELLO WORLD');

Output: 'hello world'

REPLACE()

Replaces a substring with another string.

Example:

SELECT REPLACE('hello world', 'world', 'universe');

Output: 'hello universe'

Data Types

i. CHAR(n): Stores a fixed-length string of up to n characters.

Example: CREATE TABLE test_table (id INT, name CHAR(50));

ii. VARCHAR(n): Stores a variable-length string of up to n characters.

Example: CREATE TABLE test_table (id INT, name VARCHAR(50));

More Examples

i. SELECT * FROM test_table WHERE name LIKE '%John%';

This query will return all rows from the table 'test_table' where the 'name' value contains the string 'John'.

ii. SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;

The 'first_name' and 'last_name' columns from the 'users' database will be concatenated in this query, which will return them as the new column 'full_name'.