String Operators and Functions (PostgreSQL)

Just like we can use operators to work with numbers, SQL also provides operators and functions to work with strings.

Using these, we can:

• join strings together

• change text to uppercase or lowercase

• find the length of a string

In this section, we’ll look at some common string operators and string functions in PostgreSQL.

---

String Operators vs String Functions

Before jumping into examples, let’s clear one thing:

• Operators → special symbols (like ||)

• Functions → reusable logic written like function_name(value)

Functions in SQL work very similarly to functions in other programming languages.

---

Joining Strings Using the || Operator

PostgreSQL provides a string operator called double pipe:

||

This operator is used to join (concatenate) two strings.

Example: Joining City Name and Country

select
  name || country
from cities;

This joins the city name and country together.

Example output:

TokyoJapan
DelhiIndia

---

Making the Result More Readable

Joining strings directly makes the result hard to read.

So we usually add a comma and space between them.

select
  name || ', ' || country
from cities;

Now the output looks much better:

Tokyo, Japan
Delhi, India

---

Renaming the Generated Column

By default, PostgreSQL gives this column a name like:

?column?

That’s not very helpful.

We can rename it using AS.

select
  name || ', ' || country as location
from cities;

Now the column name clearly explains what the data represents.

---

Joining Strings Using CONCAT()

PostgreSQL also provides a function called CONCAT().

It does the same thing as the || operator.

Example

select
  concat(name, country)
from cities;

This produces the same result as:

name || country

---

Adding a Comma and Space with CONCAT()

select
  concat(name, ', ', country) as location
from cities;

This is often easier to read than using multiple || operators.

---

Uppercase and Lowercase Functions

PostgreSQL provides functions to change text case:

• UPPER() → converts text to uppercase

• LOWER() → converts text to lowercase

---

Example: Converting Name and Country to Uppercase

select
  upper(name),
  upper(country)
from cities;

This returns all values in capital letters.

---

Stacking Functions Together

We can combine multiple functions.

Example: Uppercase + Concatenation

select
  upper(concat(name, ', ', country)) as location
from cities;

Here:

1. concat() joins the strings

2. upper() converts the final result to uppercase

Functions can be nested like this without any problem.

---

LOWER() Works the Same Way

select
  lower(concat(name, ', ', country)) as location
from cities;

This converts everything to lowercase.

---

The LENGTH() Function

The LENGTH() function returns the number of characters in a string.

Example

select
  name,
  length(name)
from cities;

This tells us how long each city name is.

LENGTH() is especially useful when:

• validating text length

• filtering strings of a certain size

• applying conditions later using WHERE

---

Key Takeaways

• || and CONCAT() are used to join strings

• AS is used to rename generated columns

• UPPER() and LOWER() change text case

• LENGTH() returns character count

• Functions can be nested together