Concatenation of columsn in Oracle sql dev

Question

I am looking to concatenate 2 columns in my sql code. I want 'Name' + 'Margin_Account' to show as 1 with a fullstop in the middle. An example: Name = XYZ, Margin_Account = A. I want a new column called 'Member_Account' to look as "XYZ.A". How can I do this? I am unsure on how to do this given I would require a fullstop in the middle of the Name + Margin_Account to make the new column. This my code:

SELECT to_char(intraday_run_time, 'HH24:Mi') as runtime,
       to_char(intraday_run_time, 'dd-mon-yyyy') as business_date,
       REPORT_AMOUNT,
       DESCRIPTION,
       MARGIN_ACCOUNT,
       NAME,
       LEGAL_NAME,
       case
       when to_char(intraday_run_time, 'HH24') < 9 then 'EARLY'
       when to_char(intraday_run_time, 'HH24') < 18 then 'MIDDLE'
       when to_char(intraday_run_time, 'HH24') >= 18 then 'LATE'
       else 'ERROR'
       end as time_of_day,
       case
       when to_char(intraday_run_time, 'dd-mon-yyy') > add_months(sysdate,-3) then '3month'
       else 'ERROR'
       end as lookback3,
       case when to_char(intraday_run_time, 'dd-mon-yyy') > add_months(sysdate,-6)
       then '6month'
       else 'ERROR'
       end as lookback6,
       case
       when to_char(intraday_run_time, 'dd-mon-yyy') > add_months(sysdate,-12)
       then '12month'
       else 'ERROR' end as lookback12
FROM   MEMBER_MANAGER.itd_margin_call_run_log_detail itd
       JOIN (
         SELECT DISTINCT
                MARGIN_CALL_RUN_DETAIL_ID,
                DESCRIPTION,
                margin_account,
                name,
                legal_name
         FROM   member_manager.margin_call_run_log_detail a 
                inner join member_manager.organization b
                on a.organization_ID=b.id
       ) des
       ON des.MARGIN_CALL_RUN_DETAIL_ID = itd.MARGIN_CALL_RUN_DETAIL_ID
WHERE  REPORT_AMOUNT IS NOT NULL
AND    DESCRIPTION in( 'Intraday Margin Call from ECS Call Amount Combined', 'Manually Added' )
AND    TRUNC(intraday_run_time) between add_months(sysdate,-12) and sysdate
order by 1 desc

This question has been asked so many times. One example: [What is the string concatenation operator in Oracle?](https://stackoverflow.com/questions/278189/what-is-the-string-concatenation-operator-in-oracle) — Jonas Metzler, Jul 19 '23 at 11:03

score 2 · Answer 1 · answered Jul 19 '23 at 10:47

2

Use the || string concatenation operator with the columns and a string literal then give the composite string an alias:

SELECT name || '.' || margin_account AS member_account,
       -- rest of your columns
FROM   -- rest of your query

answered Jul 19 '23 at 10:47

MT0

143,790
11
59
117

score 1 · Answer 2 · answered Jul 19 '23 at 12:35

Concatenation, as you said. In Oracle, there are two ways to do that:

a double pipe || operator
- it lets you concatenate as many values as you want
concat function
- it accepts only two parameters, which means that - if you want to concatenate two columns and put a dot in between - you'll have to nest two concats (line #6)

Example:

SQL> with test (name, margin_account) as
  2    (select 'XYZ', 'A' from dual)
  3  select
  4    name ||'.'|| margin_account as member_account,
  5    --
  6    concat(concat(name, '.'), margin_account) as member_account_2
  7  from test;

MEMBER_ACCOUNT       MEMBER_ACCOUNT_2
-------------------- --------------------
XYZ.A                XYZ.A

SQL>

Concatenation of columsn in Oracle sql dev

2 Answers2