4

I have one requirement to concat user first_ name, and last_name with space in between in Oracle. Ex: first_name is 'Hopkins' and last_name is 'Joe'.

Full name should be printed as Hopkins Joe.

I'm using Oracle 11g and it is working in SQL query, but not working in stored procedure.

Ram
  • 45
  • 1
  • 3
  • 10

5 Answers5

6

Try this: 

 SELECT CONCAT(CONCAT(first_name, ' '),last_name)

OR

 SELECT first_name || ' ' || last_namefrom;
Jibin Balachandran
  • 3,381
  • 1
  • 24
  • 38
  • Hi everyone, thanks for your answers. All solutions are working fine in normal sql query. But not working any solution in Store procedure. If I place this code store proc its giving compilation error. Please can you try to put your solutions in store procedures and post me.. – Ram Oct 17 '16 at 10:34
  • @suddewarRamu Can u post your store procedure code?? – Jibin Balachandran Oct 17 '16 at 11:41
  • 1
    What is "not working"? Please don't just say it's not working without giving the code you tried and the exact error message you got. – William Robertson Oct 17 '16 at 15:50
2

use this:

TRIM(FIRST_NAME || ' ' || LAST_NAME)

if any of first_name or last_name is blank or null the extra space we are adding will be trimmed.

ejazazeem
  • 542
  • 7
  • 10
1

Try this 

select first_name || ' ' || last_name as full_name from table

Example:

SELECT 'Dave' || ' ' || 'Anderson' as full_name
FROM table;

Result: 'Dave Anderson'
JYoThI
  • 11,977
  • 1
  • 11
  • 26
  • Hi guys, almost all the solutions are working fine with minor changes that instead of select first_name || ' ' || last_name as full_name from table, I had do like select first_name || '' '' || last_name as full_name from table and instead of SELECT CONCAT(CONCAT(first_name, '' ''),last_name) I had to change to SELECT CONCAT(CONCAT(first_name, ' '),last_name). Anyhow thank you very much. – Ram Oct 18 '16 at 13:47
1

No need to use CONCAT function twice. Concat with space will work in this way 

SELECT CONCAT(first_name,(' '||last_name)) AS full_name
shanika yrs
  • 303
  • 1
  • 13
0

This will work:

select first_name||' '||last_name
from table_name
where first_name is not null  -- "if the first_name can be null"
and   last_name is not null   -- "if the last_name can be null"
;
wieseman
  • 176
  • 10
  • Hi guys, below is the store proc. – Ram Oct 17 '16 at 18:11
  • Below is the store proc. – Ram Oct 17 '16 at 18:12
  • create or REPLACE PROCEDURE concat_names AS BEGIN DBMS_OUTPUT.PUT_LINE('empId is' || 1255); DBMS_output.put_line('Full Name is: ' || CONCAT(CONCAT('Hopkins', ' '),'Joe')); DBMS_output.put_line('Full Name with id is: ' || 'Hopkins' || ' ' || 'Joe' ||' '|| 1255 ); END ; set serveroutput on; exec concat_names(); – Ram Oct 17 '16 at 18:12
  • This is working fine in windows, but not in mac OS. Anyhow I will post the error tomorrow once I'm in office. – Ram Oct 17 '16 at 18:14