Add SPACE to CONCAT with SUBSTR Oracle SQL

Question

I'm trying to join two columns together with a space separated and the first column requiring a SUBSTR and the other not. I have written this query:

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

However I have tried numerous searching online and tried different ways to get it to work and I cannot. I get numerous errors. The result I am trying to get is for example if the raw data was Ray Adams then the result would be R Adams. Any help would be appreciated.

Answer 1

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;

Answer 2

CONCAT only allows you to concatenate 2 strings together, not more. You should use:

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