String Concatenation Operator in Oracle, Postgres and SQL Server

What is the string concatenation operator in Oracle?

It is ||, for example:

select 'Mr ' || ename from emp;

The only "interesting" feature I can think of is that 'x' || null returns 'x', not null as you might perhaps expect.

Why || is used as string concatenation in PostgreSQL/Redshift

-- DB2 / Oracle / Postgres / ANSI Standard
SELECT first_name || ' ' || last_name As full_name FROM customers;
-- Sybase / SQL Server / Microsoft Access
SELECT FirstName + ' ' + LastName As FullName FROM Customers;
-- MySQL
SELECT CONCAT(`FirstName`, ' ', `LastName`) As `FullName` FROM `Customers`;

Double pipe concatenation is part of the ANSI SQL standard. SQL was initially developed at IBM deep in the mainframe era. Most of the "major programming languages" you are thinking of did not exist when SQL was created. Most modern languages are "C like" on some level but FORTRAN77, for example, uses // as it's concatenation operator.

What is the difference between || operator and concat function in Oracle?

There is no functional difference.

|| is the ANSI standard string concatenation operator (though, unfortunately, not every database <cough>SQL Server</cough> chooses to support the standard). Many databases support a CONCAT function so it may be easier to port code using CONCAT to different databases.

Query performance with concatenation and LIKE

While not a concrete answer, the following might help you to reach some conclusions:

  1. Calling concat to concatenate the three strings, or using the || operator, results in postgres having to allocate a new buffer to hold the concatenated string, then copy the characters into it. This has to be done for each row. Then the buffer has to be deallocated at the end.

  2. In the case where you are ORing together three conditions, postgres may only have to evaluate only one or maybe two of them to decide if it has to include the row.

  3. It is possible that expression evaluation using the || operator might be more efficient, or perhaps more easily optimizable, compared with a function call to concat. I would not be surprised to find that there is some special case handling for internal operators.

  4. As mentioned in the comments, your sample is too small to make proper conclusions anyway. At the level of a fraction of a millisecond, other noise factors can distort the result.

Database-independent SQL String Concatenation in Rails

I had the same problem and never came up with anything that was built into Rails. So I wrote this little method.

# Symbols should be used for field names, everything else will be quoted as a string
def db_concat(*args)

adapter = configurations[RAILS_ENV]['adapter'].to_sym
args.map!{ |arg| arg.class==Symbol ? arg.to_s : "'#{arg}'" }

case adapter
when :mysql
"CONCAT(#{args.join(',')})"
when :sqlserver
args.join('+')
else
args.join('||')
end

end

I'm thinking somebody should really write some sort of SQL helper plugin that could automatically format simple SQL expressions based using the correct functions or operators for the current adapter. Maybe I'll write one myself.



Related Topics



Leave a reply



Submit