String Functions

10 Functions

String functions are used to manipulate and transform text data. Whether you need to clean up user input, format reports, or extract specific patterns from a dataset, these functions are your primary tools for text processing.

Table of Contents

CONCAT() & CONCAT_WS()SUBSTRING() / SUBSTR()UPPER() & LOWER()TRIM() / LTRIM() / RTRIM()LENGTH()REPLACE()LEFT() & RIGHT()POSITION() / STRPOS()SPLIT_PART()LPAD() & RPAD()
1.

CONCAT() & CONCAT_WS()

Description

Joins two or more strings into one. CONCAT_WS (With Separator) uses the first argument as a delimiter between the others.

Syntax
CONCAT(str1, str2, ...) or CONCAT_WS('-', str1, str2)
When to Use

Combining a first_name and last_name into a full_name.

Example Query
-- Standard concatenation
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;
-- Concatenation with a separator (hyphen)
SELECT CONCAT_WS('-', year, month, day) AS formatted_date FROM logs;
💡 Pro Tip

Unlike the || operator, CONCAT() handles NULL values gracefully by treating them as empty strings, preventing your entire result from becoming NULL.

2.

SUBSTRING() / SUBSTR()

Description

Extracts a specific portion of a string based on a starting position and length.

Syntax
SUBSTRING(string FROM start_position FOR length)
When to Use

Extracting the area code from a phone number or the domain from an email.

Example Query
-- Extract characters 1 to 6
SELECT SUBSTRING('XQLora-Platform' FROM 1 FOR 6); -- Result: 'XQLora'
💡 Pro Tip

In PostgreSQL, you can use Regular Expressions inside SUBSTRING. For example, SUBSTRING(email FROM '@(.*)') will extract everything after the '@' symbol.

3.

UPPER() & LOWER()

Description

Converts all characters in a string to uppercase or lowercase.

Syntax
UPPER(string) / LOWER(string)
When to Use

Standardizing user input for case-insensitive searches (e.g., searching for "john" in a "John" column).

Example Query
SELECT * FROM users WHERE LOWER(email) = 'user@example.com';
💡 Pro Tip

Always use LOWER() on both sides of a comparison in a WHERE clause to ensure you don't miss records due to casing differences.

4.

TRIM() / LTRIM() / RTRIM()

Description

Removes leading and trailing spaces (or specified characters) from a string.

Syntax
TRIM(string)
When to Use

Cleaning data where users accidentally typed spaces before or after their username.

Example Query
SELECT TRIM('   clean_me   '); -- Result: 'clean_me'
💡 Pro Tip

You can trim specific characters, not just spaces! TRIM(BOTH 'x' FROM 'xXQLorax') will result in 'XQLora'.

5.

LENGTH()

Description

Returns the number of characters in a string.

Syntax
LENGTH(string)
When to Use

Validating that a password meets a minimum character requirement.

Example Query
SELECT username FROM users WHERE LENGTH(password) < 8;
💡 Pro Tip

LENGTH() counts characters, while OCTET_LENGTH() counts bytes. For standard English, they are the same, but for emojis or special characters, OCTET_LENGTH will be higher.

6.

REPLACE()

Description

Replaces all occurrences of a specified substring within a string with a new substring.

Syntax
REPLACE(string, old_text, new_text)
When to Use

Updating an old company domain in email addresses to a new one.

Example Query
SELECT REPLACE(email, '@oldcorp.com', '@newcorp.com') FROM employees;
💡 Pro Tip

REPLACE is case-sensitive. If you need a case-insensitive replacement, you might need to use REGEXP_REPLACE with the 'i' flag.

7.

LEFT() & RIGHT()

Description

Returns a specified number of characters from the left or right side of a string.

Syntax
LEFT(string, n) / RIGHT(string, n)
When to Use

Getting the last 4 digits of a credit card number for display.

Example Query
SELECT CONCAT('****', RIGHT(card_number, 4)) FROM payments;
💡 Pro Tip

If you provide a negative number to LEFT(string, -n), it returns all characters except the last 'n' characters. Very useful for trimming file extensions!

8.

POSITION() / STRPOS()

Description

Returns the location (index) of a specific substring within a string.

Syntax
POSITION(substring IN string)
When to Use

Finding where the space character is in a full name so you can split it.

Example Query
SELECT POSITION(' ' IN 'Imam Dwi'); -- Result: 5
💡 Pro Tip

If the substring is not found, it returns 0. This is a great way to check if a string contains a specific word in a WHERE clause: WHERE POSITION('Urgent' IN subject) > 0.

9.

SPLIT_PART()

Description

Splits a string by a delimiter and returns the part at the specified index.

Syntax
SPLIT_PART(string, delimiter, part_number)
When to Use

Getting just the first name from a comma-separated "Lastname, Firstname" format.

Example Query
SELECT SPLIT_PART('Purwanto, Imam', ', ', 2); -- Result: 'Imam'
💡 Pro Tip

This is much faster and cleaner than combining SUBSTRING and POSITION for extracting data from CSV-formatted columns.

10.

LPAD() & RPAD()

Description

Pads a string to a specific length by adding characters to the left or right.

Syntax
LPAD(string, total_length, pad_character)
When to Use

Formatting ID numbers to be 5 digits long (e.g., turning "42" into "00042").

Example Query
SELECT LPAD(user_id::text, 5, '0') FROM users;
💡 Pro Tip

This is perfect for generating fixed-width files or ensuring sorting works correctly on numeric strings (so '10' doesn't come before '2').