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 tex
MSMuhammad SufiyanSoftware Engineer · Jul 26
Backend Engineering HubT-
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
-
HIRINGMINE CAREER SIGNAL
This writing is proof of expertise.
Explore the author’s verified skills, projects and availability—or start a professional conversation.
Joining Strings Using the