SQLScalar and Aggregate Functionsbeginner45 min read

String Functions

Learn to transform, clean, inspect, extract, and search text using PostgreSQL string functions, including UPPER, LOWER, INITCAP, TRIM, SUBSTRING, LENGTH, REPLACE, POSITION, and CONCAT.

On This Page (Table of Contents)

In this lesson, you will learn

✓Convert text to uppercase, lowercase, and title-style capitalization using UPPER(), LOWER(), and INITCAP().
✓Remove unwanted leading and trailing characters using TRIM(), LTRIM(), and RTRIM().
✓Measure string length using LENGTH() and understand how PostgreSQL counts characters.
✓Extract parts of strings using SUBSTRING() and find text positions using POSITION().
✓Replace text using REPLACE() and combine strings using CONCAT() and the concatenation operator.
✓Understand how string functions behave when their arguments contain NULL.
✓Clean common data-quality problems such as inconsistent capitalization and surrounding spaces.
✓Recognize performance considerations when applying string functions in WHERE conditions.

1. Introduction to String Functions

A string is a sequence of characters, such as a person's name, an email address, a product description, or a phone number stored as text.

Real-world data is not always consistent. One customer might be stored as 'SATYAM', another as 'satyam', and another as ' Satyam '. These values may look different even when they are intended to represent the same name.

SQL string functions let you inspect and transform text directly in the database. They are useful for reporting, searching, data cleaning, formatting, and preparing values for applications.

In this lesson, examples use PostgreSQL syntax. Some functions and details vary between database systems.

A Simple String Transformation

sql

1
2
3
4
SELECT
  UPPER('satyam') AS uppercase_name,
  LOWER('SATYAM') AS lowercase_name,
  LENGTH('SQL') AS character_count;

Output

uppercase_name = SATYAM, lowercase_name = satyam, character_count = 3.

UPPER() changes the letter case, LOWER() converts letters to lowercase, and LENGTH() counts characters in the supplied text.

2. UPPER() — Convert Text to Uppercase

UPPER(text) converts lowercase letters in a string to uppercase. Characters that do not have an uppercase equivalent remain unchanged.

This function is useful when displaying standardized labels or performing case-insensitive comparisons. The conversion follows the database's text and collation behavior; it is not a universal language-aware identity-matching solution.

Convert Names to Uppercase

sql

1
2
3
SELECT
  UPPER('satyam katiyar') AS formatted_name,
  UPPER('codenivo sql') AS platform_name;

Output

formatted_name = SATYAM KATIYAR, platform_name = CODENIVO SQL.

The function converts letters to uppercase while preserving spaces and other characters.

3. LOWER() — Convert Text to Lowercase

LOWER(text) converts letters to lowercase. It is useful for standardizing text comparisons, preparing search values, and displaying consistently formatted data.

For example, you may want an email address comparison to ignore differences in letter case. Applying LOWER() to both sides is one possible approach, but the correct strategy depends on the application's email identity rules and database design.

Normalize Text Case

sql

1
2
3
SELECT
  LOWER('SATYAM@EXAMPLE.COM') AS normalized_email,
  LOWER('HELLO SQL') AS message;

Output

normalized_email = satyam@example.com, message = hello sql.

LOWER() converts letters to lowercase. It does not remove surrounding spaces or validate the email address.

4. INITCAP() — Capitalize Words

PostgreSQL's INITCAP(text) converts the first letter of each word to uppercase and the remaining letters of each word to lowercase according to its word-boundary rules.

It can help format names and headings, but real names do not always follow simple title-style capitalization rules. Names such as McDonald, O'Neill, or names with special capitalization may require application-specific handling.

Capitalize a Name

sql

1
2
3
SELECT
  INITCAP('satyam katiyar') AS formatted_name,
  INITCAP('INTRODUCTION TO SQL') AS formatted_title;

Output

formatted_name = Satyam Katiyar, formatted_title = Introduction To Sql.

INITCAP() applies title-style capitalization to words. It does not understand every person's preferred spelling or every publication's title-style rules.

5. Comparing UPPER(), LOWER(), and INITCAP()

These three functions change capitalization, but they produce different display formats.

Case-conversion functions

Function	Input	Illustrative output
UPPER()	hello sql	HELLO SQL
LOWER()	HELLO SQL	hello sql
INITCAP()	hello sql	Hello Sql
UPPER()	Satyam	SATYAM
LOWER()	Satyam	satyam
INITCAP()	SATYAM KATIYAR	Satyam Katiyar

6. TRIM() — Remove Surrounding Characters

TRIM() removes selected characters from the beginning, end, or both ends of a string. Without an explicit direction or character set, TRIM() removes ordinary space characters from both ends.

Leading spaces occur before the text, and trailing spaces occur after it. These can cause inconsistent display or unexpected comparisons.

In PostgreSQL, TRIM(BOTH 'x' FROM text) removes occurrences of the specified character from both ends. It does not remove that character from the middle of the string.

Remove Surrounding Spaces

sql

1
2
3
SELECT
  TRIM('  Satyam  ') AS cleaned_name,
  TRIM(BOTH '-' FROM '---SQL---') AS cleaned_code;

Output

cleaned_name = Satyam, cleaned_code = SQL.

The first expression removes surrounding spaces. The second removes hyphens from the beginning and end, but not from the middle.

7. LTRIM() and RTRIM()

LTRIM(text) removes leading characters from a string. RTRIM(text) removes trailing characters. Without a specified character set, PostgreSQL removes ordinary spaces.

You can also specify a set of characters to remove. The characters argument represents a set of characters, not a complete substring that must match as a unit.

Remove Left and Right Spaces

sql

1
2
3
4
5
SELECT
  LTRIM('   SQL') AS left_cleaned,
  RTRIM('SQL   ') AS right_cleaned,
  LTRIM('---SQL', '-') AS left_hyphens_removed,
  RTRIM('SQL---', '-') AS right_hyphens_removed;

Output

left_cleaned = SQL, right_cleaned = SQL, left_hyphens_removed = SQL, right_hyphens_removed = SQL.

LTRIM() works on the left side and RTRIM() works on the right side. Specifying '-' removes that character from the relevant edge.

8. TRIM(), LTRIM(), and RTRIM() Compared

Use the direction that matches the cleaning requirement. Removing characters from both ends is different from removing them from just one side.

Trimming functions

Function	What it removes	Example
TRIM(text)	Spaces at both ends	TRIM(' SQL ') → SQL
LTRIM(text)	Leading spaces	LTRIM(' SQL') → SQL
RTRIM(text)	Trailing spaces	RTRIM('SQL ') → SQL
TRIM(BOTH '-' FROM text)	Hyphens at both ends	TRIM(BOTH '-' FROM '--SQL--') → SQL

9. LENGTH() — Count Characters

LENGTH(text) returns the number of characters in a PostgreSQL text value. Spaces and punctuation count as characters.

For example, LENGTH('SQL Basics') returns 10 because the space between the words also counts.

Character count is not necessarily the same as byte count. Some characters require multiple bytes in UTF-8 encoding. In PostgreSQL, OCTET_LENGTH() measures the number of bytes in a value.

Measure Text Length

sql

1
2
3
4
5
SELECT
  LENGTH('SQL') AS short_length,
  LENGTH('SQL Basics') AS title_length,
  LENGTH('  SQL  ') AS spaced_length,
  OCTET_LENGTH('SQL') AS byte_length;

Output

short_length = 3, title_length = 10, spaced_length = 7, byte_length = 3.

LENGTH() counts characters, including spaces. OCTET_LENGTH() counts bytes in the encoded value.

10. SUBSTRING() — Extract Part of a String

SUBSTRING() extracts part of a string. In PostgreSQL, SUBSTRING(text FROM start FOR count) uses a one-based starting position.

That means the first character is at position 1, not position 0. If you omit FOR count, the substring extends from the starting position to the end of the string.

SUBSTRING() is useful for extracting prefixes, codes, parts of identifiers, or portions of text with a predictable structure.

Extract Parts of a String

sql

1
2
3
4
SELECT
  SUBSTRING('CodeNivo' FROM 1 FOR 4) AS prefix,
  SUBSTRING('CodeNivo' FROM 5 FOR 4) AS suffix,
  SUBSTRING('PostgreSQL' FROM 5) AS remaining_text;

Output

prefix = Code, suffix = Nivo, remaining_text = greSQL.

The first expression starts at character 1 and extracts four characters. The second starts at character 5. The third returns the remainder starting at character 5.

11. Understanding SUBSTRING() Positions

Suppose the string is 'SQL2026'. Its positions are:

S = 1, Q = 2, L = 3, 2 = 4, 0 = 5, 2 = 6, 6 = 7.

SUBSTRING('SQL2026' FROM 4 FOR 4) returns '2026'.

The starting position and requested length are separate values. A wrong starting position can return the wrong portion of a string even when the query runs successfully.

Extract a Four-Digit Year

sql

1
2
3
SELECT
  SUBSTRING('SQL2026' FROM 4 FOR 4) AS year_text,
  LENGTH(SUBSTRING('SQL2026' FROM 4 FOR 4)) AS year_length;

Output

year_text = 2026, year_length = 4.

The year is extracted as text. If you need a numeric year for arithmetic or date logic, explicitly convert it after validating the input format.

12. REPLACE() — Replace Matching Text

REPLACE(source, search_text, replacement_text) replaces all occurrences of the search text in the source string.

For example, REPLACE('sql basics', 'basics', 'functions') returns 'sql functions'. If the search text appears more than once, every occurrence is replaced.

REPLACE() performs literal substring replacement. It does not interpret the search text as a regular expression. For regular-expression replacement in PostgreSQL, use REGEXP_REPLACE() when its pattern-based behavior is needed.

Replace Text in a String

sql

1
2
3
SELECT
  REPLACE('sql basics', 'basics', 'functions') AS updated_title,
  REPLACE('2026-10-18', '-', '/') AS formatted_date_text;

Output

updated_title = sql functions, formatted_date_text = 2026/10/18.

The first query replaces a word. The second replaces every hyphen with a slash. The second result is still text, not a DATE value.

13. POSITION() — Find Text Within a String

POSITION(substring IN string) returns the position of the first occurrence of a substring in the specified string. PostgreSQL uses one-based positions.

If the substring is not found, POSITION() returns 0. This makes it useful for checking whether a string contains a particular sequence, although the expression must be used correctly when writing conditions.

POSITION() finds a literal substring. It does not accept a regular expression.

Find a Substring

sql

1
2
3
SELECT
  POSITION('SQL' IN 'Learn SQL today') AS sql_position,
  POSITION('Python' IN 'Learn SQL today') AS python_position;

Output

sql_position = 7, python_position = 0.

The first occurrence of SQL starts at position 7. Python is not present, so the result is 0.

14. Combining Strings with CONCAT()

CONCAT() combines two or more values into a single text result. In PostgreSQL, CONCAT() converts its arguments to text where needed and ignores NULL arguments.

This can be useful when creating display names from first and last names. However, ignoring NULL is not always the desired business behavior, and concatenating names does not automatically insert spaces.

Combine First and Last Names

sql

1
2
3
4
SELECT
  CONCAT('Satyam', ' ', 'Katiyar') AS full_name,
  CONCAT('Code', 'Nivo') AS platform_name,
  CONCAT('Hello', NULL, ' SQL') AS null_example;

Output

full_name = Satyam Katiyar, platform_name = CodeNivo, null_example = Hello SQL.

CONCAT() combines the supplied text. The explicit space in the first expression separates the name parts. PostgreSQL CONCAT() ignores NULL arguments.

15. The || Concatenation Operator

PostgreSQL also supports the || operator for string concatenation. Unlike CONCAT(), the || operator generally produces NULL when an operand is NULL.

Choose CONCAT() or || based on the desired NULL behavior and your team's conventions. CONCAT_WS() can be useful when combining values with a separator while skipping NULL values after the separator argument.

Compare Concatenation Behavior

sql

1
2
3
4
5
6
SELECT
  'Code' || 'Nivo' AS operator_result,
  CONCAT('Code', 'Nivo') AS concat_result,
  'Hello' || NULL AS operator_with_null,
  CONCAT('Hello', NULL) AS concat_with_null,
  CONCAT_WS(' ', 'Satyam', NULL, 'Katiyar') AS name_with_separator;

Output

operator_result = CodeNivo, concat_result = CodeNivo, operator_with_null = NULL, concat_with_null = Hello, name_with_separator = Satyam Katiyar.

The || operator propagates NULL. CONCAT() ignores NULL arguments. CONCAT_WS() inserts a separator between non-NULL values after its first argument.

16. Practical Example: Clean Customer Names

Customer data can contain inconsistent capitalization and extra spaces. You can combine TRIM() and INITCAP() to produce a more consistent display value.

The query below shows a cleaned value without changing the stored table. This is a safe first step when exploring data quality because it lets you inspect the transformation before updating any records.

Note that INITCAP() is a formatting function, not a universal personal-name normalization system.

Display Cleaned Names

sql

1
2
3
4
5
6
SELECT
  customer_id,
  full_name AS original_name,
  INITCAP(TRIM(full_name)) AS cleaned_name
FROM customers
ORDER BY customer_id;

Output

Returns each customer's ID, original name, and a display value with surrounding spaces removed and title-style capitalization applied.

The source column is not changed. The cleaned value is calculated for the query result only.

17. Practical Example: Normalize Email Text

A report may need to display email addresses in lowercase and without surrounding spaces. You can use LOWER(TRIM(email)) to create a normalized output value.

This query does not update the stored data and does not validate whether an email address exists or is deliverable. Whether email comparisons should be case-insensitive is an application-level decision that should be made consistently.

Prepare Email Values for Review

sql

1
2
3
4
5
6
SELECT
  customer_id,
  email AS original_email,
  LOWER(TRIM(email)) AS normalized_email
FROM customers
WHERE email IS NOT NULL;

Output

Returns customers with non-NULL email values, showing the original value and a lowercase version with surrounding spaces removed.

WHERE excludes NULL email values. LOWER() and TRIM() transform the displayed result without modifying the underlying table.

18. Practical Example: Search for a Substring

POSITION() can help identify rows containing a literal substring. A result greater than zero means the substring was found.

The following example searches product names for the text 'pro'. This search is case-sensitive in the illustrated form. If you need case-insensitive matching, you can normalize both values with LOWER() or use a suitable PostgreSQL pattern-matching operation.

Find Product Names Containing Text

sql

1
2
3
4
5
SELECT
  product_id,
  product_name
FROM products
WHERE POSITION('pro' IN product_name) > 0;

Output

Returns products whose product_name contains the case-sensitive substring 'pro'. The actual rows depend on the stored data.

POSITION() returns a positive number when the substring is found and zero otherwise.

19. String Functions and NULL

NULL represents a missing or unknown value, not an empty string. PostgreSQL's string functions do not all treat NULL the same way.

For example, UPPER(NULL), LOWER(NULL), TRIM(NULL), LENGTH(NULL), SUBSTRING(NULL FROM 1 FOR 2), REPLACE(NULL, 'a', 'b'), and POSITION('a' IN NULL) return NULL.

CONCAT() is an important exception: it ignores NULL arguments. The || operator generally returns NULL if either operand is NULL.

Use COALESCE() when you intentionally want a fallback value, but only when that fallback matches the business meaning of the data.

Handle Missing Text Values

sql

1
2
3
4
5
6
SELECT
  UPPER(NULL) AS uppercase_null,
  LENGTH(NULL) AS null_length,
  CONCAT('Name: ', NULL) AS concat_result,
  'Name: ' || NULL AS operator_result,
  COALESCE(TRIM(NULL), 'Unknown') AS fallback_name;

Output

uppercase_null = NULL, null_length = NULL, concat_result = Name: , operator_result = NULL, fallback_name = Unknown.

Most of these functions propagate NULL. CONCAT() ignores its NULL argument, while COALESCE() deliberately substitutes a fallback value.

20. Empty Strings, Spaces, and NULL

An empty string contains zero characters. A string containing spaces has characters even if they are not visually obvious. NULL represents a missing or unknown value.

These values may require different treatment in data cleaning. TRIM() can turn a string containing only ordinary spaces into an empty string, but it does not turn that empty string into NULL automatically.

Different text states

Value	Meaning	LENGTH() in PostgreSQL
'SQL'	Three characters	3
''	Empty string	0
' '	Three ordinary spaces	3
NULL	Missing or unknown value	NULL
TRIM(' ')	Empty string after trimming	0

21. Updating Stored Text Safely

Most examples in this lesson use SELECT, so they calculate transformed values without changing stored records.

If you decide to clean the actual table, an UPDATE statement can store the transformed value. Before doing so, inspect the affected rows with SELECT and confirm the intended transformation. Consider backing up the data or using a transaction according to your operational process.

Be especially careful with name formatting, email normalization, and replacing characters. A transformation that looks correct for one record may corrupt another record if the data has different conventions.

Preview a Transformation Before Updating

sql

1
2
3
4
5
6
7
-- Preview the proposed transformation first.
SELECT
  customer_id,
  full_name,
  INITCAP(TRIM(full_name)) AS proposed_name
FROM customers
WHERE full_name IS NOT NULL;

Output

Shows original names beside proposed cleaned values. No rows are changed.

Review the output first. Only use UPDATE after confirming that the transformation is appropriate for the real data.

22. String Functions and Index Performance

A condition such as LOWER(email) = 'user@example.com' applies a function to the column. A regular index on email may not be the most suitable index for that expression.

In PostgreSQL, an expression index can be created for a frequently used expression, for example CREATE INDEX ... ON customers (LOWER(email)). The planner may use it when the query expression and other conditions make that index useful.

Do not add indexes automatically. Indexes consume storage and add work to inserts, updates, and deletes. Check the query plan and the application's workload before deciding.

Also note that TRIM(), LOWER(), and other transformations have specific semantics. A case-normalized expression index does not automatically solve every Unicode normalization or linguistic comparison requirement.

An Expression Index for Lowercase Email Search

sql

1
2
3
4
5
6
7
8
9
10
-- Optional: create only when justified by the workload.
CREATE INDEX idx_customers_lower_email
ON customers (LOWER(email));
 
-- Query using the matching expression.
SELECT
  customer_id,
  email
FROM customers
WHERE LOWER(email) = 'user@example.com';

Output

The query returns matching customer rows. Whether PostgreSQL uses the expression index depends on the data, statistics, query, and execution plan.

The index is an optional optimization, not a requirement for string functions to work. Evaluate its usefulness with EXPLAIN and realistic data.

23. Guided Practice

Use PostgreSQL to practise each string function. These exercises use literal values unless a table is specified.

Task 1: Convert 'hello sql' to uppercase.

Task 2: Convert 'CODENIVO' to lowercase.

Task 3: Apply title-style capitalization to 'learn sql functions'.

Task 4: Remove surrounding spaces from ' PostgreSQL '.

Task 5: Extract 'Nivo' from 'CodeNivo'.

Task 6: Count the characters in 'SQL Basics'.

Task 7: Replace every hyphen in '2026-10-18' with a slash.

Task 8: Find the position of 'SQL' in 'Learn SQL today'.

Task 9: Combine 'Satyam' and 'Katiyar' with a space between them.

Task 10: Display each customer's original name and a proposed cleaned version using TRIM() and INITCAP().

24. Guided Practice Solutions

Try the tasks yourself before comparing your answers. These examples use PostgreSQL syntax.

Tasks 1–4: Case and Trimming

sql

1
2
3
4
5
6
7
8
9
10
11
-- Task 1
SELECT UPPER('hello sql');
 
-- Task 2
SELECT LOWER('CODENIVO');
 
-- Task 3
SELECT INITCAP('learn sql functions');
 
-- Task 4
SELECT TRIM('  PostgreSQL  ');

Output

HELLO SQL; codenivo; Learn Sql Functions; PostgreSQL.

UPPER(), LOWER(), INITCAP(), and TRIM() each perform a different text transformation.

25. More Practice Solutions

The next examples cover substring extraction, measuring length, replacement, searching, concatenation, and cleaning stored values for display.

Tasks 5–10

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- Task 5
SELECT SUBSTRING('CodeNivo' FROM 5 FOR 4);
 
-- Task 6
SELECT LENGTH('SQL Basics');
 
-- Task 7
SELECT REPLACE('2026-10-18', '-', '/');
 
-- Task 8
SELECT POSITION('SQL' IN 'Learn SQL today');
 
-- Task 9
SELECT CONCAT('Satyam', ' ', 'Katiyar');
 
-- Task 10
SELECT
  customer_id,
  full_name AS original_name,
  INITCAP(TRIM(full_name)) AS cleaned_name
FROM customers;

Output

Nivo; 10; 2026/10/18; 7; Satyam Katiyar; and a customer listing with original and proposed cleaned names.

These functions operate on text. They do not update stored records unless used in a data-modification statement.

26. Multiple-Choice Questions

Choose the best answer for each question. Answers and explanations are provided so you can review the concepts.

27. SQL Interview Questions and Answers

Q1. What are SQL string functions?
A: Functions that inspect, transform, extract, or combine text values.

Q2. What does UPPER() do?
A: Converts letters in a string to uppercase.

Q3. What does LOWER() do?
A: Converts letters in a string to lowercase.

Q4. What does INITCAP() do in PostgreSQL?
A: Capitalizes the first letter of each word and lowercases the remaining letters according to its word-boundary rules.

Q5. What is the purpose of TRIM()?
A: Removes specified characters from the beginning, end, or both ends of a string.

Q6. What is the difference between LTRIM() and RTRIM()?
A: LTRIM() removes characters from the left; RTRIM() removes them from the right.

Q7. What does LENGTH() return in PostgreSQL?
A: The number of characters in the text value.

Q8. How is LENGTH() different from OCTET_LENGTH()?
A: LENGTH() counts characters, while OCTET_LENGTH() counts bytes.

Q9. What does SUBSTRING() do?
A: Extracts part of a string based on a starting position and, optionally, a length.

Q10. Are substring positions zero-based in PostgreSQL?
A: No. Positions are generally one-based, so the first character is at position 1.

Q11. What does REPLACE() do?
A: Replaces every occurrence of a specified literal substring with replacement text.

Q12. Does REPLACE() use regular expressions?
A: No. PostgreSQL REGEXP_REPLACE() is used for regular-expression replacement.

Q13. What does POSITION() return if the substring is absent?
A: Zero.

Q14. What is the difference between CONCAT() and || in PostgreSQL?
A: CONCAT() ignores NULL arguments, while || generally returns NULL if either operand is NULL.

Q15. Does CONCAT() insert spaces automatically?
A: No. Supply a space explicitly or use CONCAT_WS() when separator behavior is appropriate.

Q16. Is an empty string the same as NULL?
A: No. An empty string contains zero characters; NULL represents a missing or unknown value.

Q17. Does TRIM() convert an empty string to NULL?
A: No. TRIM() can return an empty string, but additional logic is needed to convert that value to NULL.

Q18. Can string functions modify stored data?
A: Not by themselves in a SELECT expression. They can be used in UPDATE statements to store transformed values.

Q19. Can LOWER() help with case-insensitive searching?
A: Yes, by normalizing text for comparison, but indexing, collation, and application requirements should be considered.

Q20. What is an expression index in PostgreSQL?
A: An index built on an expression, such as LOWER(email), which may help queries using a matching expression.

Q21. Does an expression index guarantee faster queries?
A: No. Its usefulness depends on the query, data, statistics, and execution plan.

Q22. Can INITCAP() correctly format every person's name?
A: No. Real names have different capitalization conventions, so the function may not preserve every preferred spelling.

Q23. Does REPLACE() change only the first occurrence?
A: No. PostgreSQL REPLACE() replaces all occurrences of the matching substring.

Q24. Does POSITION() search with a regular expression?
A: No. It searches for a literal substring.

Q25. What should you do before updating a table with string transformations?
A: Preview the transformed values, validate the business rules, and use an appropriate backup or transaction strategy before making changes.

28. Lesson Summary and Final Checklist

You have learned how to transform, clean, inspect, and search text using PostgreSQL string functions.

- UPPER() converts text to uppercase.
- LOWER() converts text to lowercase.
- INITCAP() applies title-style capitalization.
- TRIM(), LTRIM(), and RTRIM() remove characters from string boundaries.
- LENGTH() counts characters; OCTET_LENGTH() counts bytes.
- SUBSTRING() extracts a portion of a string using one-based positions in PostgreSQL.
- REPLACE() replaces all occurrences of literal text.
- POSITION() finds a substring and returns zero when it is absent.
- CONCAT() combines values and ignores NULL arguments in PostgreSQL.
- The || operator generally propagates NULL.
- NULL, an empty string, and a string containing spaces are different values.
- Expression indexes may help specific searches, but performance must be verified.
- Preview data-cleaning transformations before storing changes in a table.

Before moving on, make sure you can explain each function, predict its output, and identify how NULL values affect the result.

29. Common Mistakes

Review these mistakes to avoid incorrect transformations and unexpected search results.

Ready for hands-on challenges?

Test your skills with shuffled coding challenges in the Practice Arena.

Practice Arena →

Key takeaways

UPPER() converts text to uppercase, while LOWER() converts text to lowercase.
INITCAP() capitalizes the first letter of each word according to PostgreSQL's word-boundary rules.
TRIM() removes specified characters from the beginning, end, or both ends of a string.
LENGTH() returns the number of characters in a PostgreSQL text value, not its byte length.
SUBSTRING() extracts part of a string, and PostgreSQL substring positions are generally one-based.
POSITION() returns the location of a substring or zero when it is not found.
REPLACE() replaces all occurrences of a matching substring.
NULL is different from an empty string, and different string functions handle NULL differently.
Applying a function to an indexed column can prevent a regular index from being used efficiently unless an appropriate expression index or other strategy is available.
String functions transform text; they do not automatically validate emails, normalize every Unicode representation, or guarantee data quality.

Was this lesson helpful?

Found an error? Report an issue