SQL String functions in SQL Server, Oracle and PostgreSQL


By:   |   Updated: 2021-10-12   |   Comments   |   Related: More > Other Database Platforms


Problem

SQL string functions are widely used to manipulate, extract, format and search text for char, nchar (unicode), varchar, nvarchar (unicode), etc. data types. Unfortunately, there are some differences with the SQL functions between SQL Server, Oracle and PostgreSQL which we will cover in this article.

Solution

In this tip we will review some of the basic string functions, the various possibilities, best practices and differences on doing operations with strings in SQL Server, Oracle and PostgreSQL.

As always, we will use the github freely downloadable database sample Chinook, as it is available in multiple RDBMS formats. It is a simulation of a digital media store, with some sample data, all you have to do is download the version you need and you have all the scripts for the data structure and inserts statements for the data.

SQL String Functions to Concatenate Strings

The first, easiest and most common operation, in my opinion, is the concatenation of two (or more) strings, so let's see various methods to do so!

SQL Server

In order to concatenate strings with T-SQL in SQL Server there are basically two methods, the first is to use the concatenate operator + as in this example we want to have First Name and Last Name of our customers returned in one column:

select FirstName + ' ' + LastName as CustomerName
from Customer
query results

Easily done, along with adding a space in the middle.

The same result can be obtained using the function CONCAT using a comma separator between each string value:

select concat(FirstName,' ',LastName) as CustomerName
from Customer
query results

Note that in the CONCAT syntax you separate the strings with a comma. Very easy.

Check out these related tutorials:

Oracle

In Oracle the concatenation operator is a double pipe ||:

select Firstname||' '||LastName as CustomerName
from chinook.customer;
query results

In Oracle the function CONCAT can only be used with two strings, so the following does not work:

select concat(FirstName,' ',LastName) as CustomerName
from chinook.customer;

We end up with an error:

query results

But this will work:

select concat(FirstName,LastName) as CustomerName
from chinook.customer;

And this is the result:

query results

PostgreSQL

In PostgreSQL the concatenation operator is the same as in Oracle:

select "FirstName"||' '||"LastName" as "CustomerName"
from "Customer"
query results

And in PostgreSQL, CONCAT works as in SQL Server:

select concat("FirstName",' ',"LastName") as "CustomerName"
from "Customer"
query results

SQL String Functions for Substring

Another typical operation is to extract some part of a string. For example, let's imagine that we need to extract the initial of every Firstname and combine it with '.' and Lastname for every customer:

SQL Server

In SQL Server we use the function SUBSTRING:

select substring(FirstName,1,1) + '. ' + LastName as CustomerName
from Customer
query results

Quite easy, the SUBSTRING function accepts the string as the first parameter, the starting point of the string as the second and the third is how many characters we need to extract.

The same results above can be achieved using the LEFT function as follows:

select left(FirstName,1) + '. ' + LastName as CustomerName
from Customer
query results

The LEFT function starts at the left of the string for X amount of characters of the string.

Obviously, there is also a RIGHT function. Let's suppose that we need to extract all the customers with names ending with a vowel:

select customerid, FirstName + ' ' + LastName as CustomerName
from Customer
where right(FirstName,1) in ('a','e','i','o','u')
query results

Oracle

In Oracle, the function is similar, although with a slightly different name, SUBSTR:

select substr(FirstName,1,1)||'. '||LastName as CustomerName
from chinook.Customer;
query results

The syntax is exactly the same as in SQL Server, just the name of the function is slightly different.

Unfortunately, in Oracle PL/SQL there is no LEFT or RIGHT functions so we will end up always using SUBSTR to extract strings.

PostgreSQL

In PostrgeSQL the function is SUBSTRING like in SQL Server:

select substring("FirstName",1,1)||'. '||"LastName" as "CustomerName"
from "Customer"
query results

We can also use the LEFT function:

select left("FirstName",1)||'. '||"LastName" as "CustomerName"
from "Customer"
query results

And use the RIGHT function:

select "CustomerId", "FirstName"||' '||"LastName" as "CustomerName"
from "Customer"
where right("FirstName",1) in ('a','e','i','o','u')
query results

Trim Leading and Trailing Spaces from a String

A similar feature to LEFT and RIGHT function are LTRIM and RTRIM, these are used to delete all spaces from the left or right of the given string. These are often used in data cleansing, so in data warehouse ETL I encountered it a lot.

SQL Server

First, we'll mess up the data by adding some spaces at the beginning of the FirstName column in all some rows:

update Customer
set FirstName = '        ' + FirstName
where right(FirstName,1) in ('a','e','i','o','u')

Let's see how the data looks now:

select FirstName
from Customer
where right(FirstName,1) in ('a','e','i','o','u')
query results

As expected, there are some spaces at the beginning of each FirstName.

So now we'll clean the data:

update Customer
set FirstName = ltrim(FirstName)
where right(Firstname,1) in ('a','e','i','o','u')

Now we can check the data again:

select FirstName
from Customer
where right(FirstName,1) in ('a','e','i','o','u')
query results

We can do the same thing with RTRIM to remove the space on the right of the string.

In SQL Server 2017 and onwards, we can remove spaces from the left and right at the same time using the TRIM function.

So, let's mess up the data again and add some spaces, this time to all rows in the table:

update Customer
set FirstName= '    ' + FirstName + '  '

Let's see our data now:

select FirstName 
from Customer
query results

Let's clean it up using TRIM:

update Customer
set FirstName = trim(FirstName)

And let's take a look now:

select firstName
from Customer
query results

Oracle

In Oracle we have the exact same functions, so let's first mess up our data:

update chinook.Customer
set FirstName='        '||FirstName
where substr(FirstName,length(FirstName),1) in ('a','e','i','o','u');
commit;

You notice that since we do not have the RIGHT function in Oracle, we must use a little workaround with the help of the LENGTH function that, guess what, returns the length value in number of characters of the string. This same function also exists in SQL Server and PostgreSQL, although in SQL Server is called LEN. Then using this number as the start point for the SUBSTR we are able to retrieve the last character of the string.

Let's take a look to the data:

select firstname
from chinook.Customer;
query results

Now let's clean it up with LTRIM:

update chinook.Customer
set FirstName=ltrim(FirstName)
where substr(FirstName,length(FirstName),1) in ('a','e','i','o','u');
commit;

And we'll take a look to the data again:

select FirstName
from chinook.Customer;
query results

Now let's mess up the data again for the TRIM function:

update chinook.Customer
set FirstName='     '||FirstName||'     ';
commit;

Then we'll take a look:

select FirstName
from chinook.Customer;
query results

And now let's clean it up:

update chinook.Customer
set FirstName=trim(FirstName);
commit;

and the data are now fine again:

query results

Note that in Oracle there is a difference as the TRIM, LTRIM and RTRIM functions can be used to remove other specified characters, not only spaces as in SQL Server. Let's do an example adding some characters at the beginning of the column first name:

update chinook.Customer
set FirstName='... '||FirstName
where substr(FirstName,length(FirstName),1) in ('a','e','i','o','u');
commit;

Let's take a look at the data now:

select FirstName
from chinook.Customer
where substr(FirstName,length(FirstName),1) in ('a','e','i','o','u');
query results

And now we clean it up using the LTRIM function:

update chinook.Customer
set FirstName=ltrim(FirstName,'. ')
where substr(FirstName,length(FirstName),1) in ('a','e','i','o','u');
commit;

Our data are back to the previous look:

query results

Pay attention that all the TRIM functions, for every character in the set, remove the right-most or left-most occurrences of each from the string. That is why if you noticed I put just one dot in the LTRIM:

set FirstName=ltrim(FirstName,'. ')

PostgreSQL

In PostgreSQL we have the same functions with the same syntax and meaning.

Let's start with messing our data:

update "Customer"
set "FirstName"='        '||"FirstName"
where right("FirstName",1) in ('a','e','i','o','u')

Taking a look at what we have now inside the table:

select "FirstName"
from "Customer"
where right("FirstName",1) in ('a','e','i','o','u')
query results

And now we clean it up with LTRIM:

update "Customer"
set "FirstName"=ltrim("FirstName")
where right("FirstName",1) in ('a','e','i','o','u')

Let's take a look again at the data now:

query results

LTRIM and RTRIM work exactly the same way, but here we have the possibility like in Oracle to remove other characters instead of the default space, let's do the same test we did in Oracle:

update "Customer"
set "FirstName"='... '||("FirstName")
where right("FirstName",1) in ('a','e','i','o','u')

and see the data:

select "FirstName"
from "Customer"
where right("FirstName",1) in ('a','e','i','o','u')
query results

Now let's clean it up:

update "Customer"
set "FirstName"=ltrim("FirstName", '. ')
where right("FirstName",1) in ('a','e','i','o','u')

and see how they are now:

query results

Replacing Text in a String

Another useful function in manipulating strings is REPLACE, as the name suggests it can be used to replace a specified string with another.

Let's do an example: let's imagine that we need to replace, in column Company, the word "Inc." with "Co.".

SQL Server

This task can be easily done using the function REPLACE, let's take a look to the data first:

SELECT customerid, Company
FROM Customer
where Company is not null
query results

Now let's update it:

update Customer
set Company = replace(Company, 'Inc.','Co.')
where Company is not null

And we can see the modifications:

query results

Easily done!

Oracle

The same functionality is in Oracle, let's check the data first:

SELECT customerid, Company
FROM chinook.Customer
where Company is not null;
query results

And now the REPLACE:

update chinook.customer
set Company=replace(Company, 'Inc.','Co.')
where Company is not null;
commit;

Then we can look at the data again:

query results

PostgreSQL

Now PostgreSQL:

SELECT "CustomerId", "Company"
FROM "Customer"
where "Company" is not null
order by "CustomerId"
query results

Please note that I needed to add an order by to have the customers in the same order as in the other two RDBMS.

Let's use REPLACE:

update "Customer"
set "Company"=replace("Company", 'Inc.','Co.')
where "Company" is not null

And a final look at the data:

query results
Next Steps





get scripts

next tip button



About the author
MSSQLTips author Andrea Gnemmi Andrea Gnemmi is a Database and Data Warehouse professional with almost 20 years of experience, having started his career in Database Administration with SQL Server 2000.

View all my tips


Article Last Updated: 2021-10-12

Comments For This Article





download














get free sql tips
agree to terms