What Is the String Concatenation Operator in Oracle

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.

What does || ' ' || mean in oracle SQL

The SQL || operator allows you to concatenate 2 or more strings together.

Example:
varchar 1: Harry -
Varchar 2: Mueller

You can concatenate this two with || operator:

Select 'Harry' || 'Mueller' from dual:

output:

HarryMueller

But with a space you can seprate these two. You can add a space between two varchar with a ' ' :

Select 'Harry' || ' ' || 'Mueller' from dual:

output:

Harry Mueller

What does a concatenate operator do inside a INSTR function?

The double pipe operator is used to do string concatenation in SQL and PL/SQL.

In your case it is used to build the lookup string from 3 parts: an initial ~, value2 and a final ~.

For instance, if value2 = 10, INSTR (value1, '~' || TO_CHAR (value2)|| '~') > 0 will be expanded to INSTR(value1, '~10~') > 0

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.

Using string concatenation in Oracle SQL Developer not outputting in desired format

seems like your datatype is just char and not var or alot of space has been saved ,the best would be to chnage your datatype to var,if not you can trim them :

SELECT TRIM(FirstName) ||', '|| TRIM(LastName)  "CustomerName"
FROM Customer;

Add SPACE to CONCAT with SUBSTR Oracle SQL

Use Concatenation Operator:

SELECT SUBSTR(FIRST_NAME,1,1)|| ' '||LAST_NAME AS NAME FROM OEHR_EMPLOYEES;

Or nested concat function:

SELECT concat(CONCAT(SUBSTR(FIRST_NAME,1,1), ' '),LAST_NAME) AS NAME FROM OEHR_EMPLOYEES;

Oracle/ PLSQL condition like 'string' || '%'

|| is concatenation operator. Oracle will first perform concatenation and then will use LIKE to match the pattern. Hence operationally it will be same as the second one.

However you should use the second one as it will be more efficient in performance and easy to read.

First one has extra overhead to append two strings before using LIKE to match the pattern.

---

Related Topics