Search code examples
sqloracleconcatenationsubstr

Add SPACE to CONCAT with SUBSTR Oracle SQL

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.

Solution

  • 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;