Concatenating values from SQL select statement

Question

I am doing a basic SQL select from an oracle database.

select DISTINCT spriden_pidm, a.saracmt_comment_text, spriden_id, c.saracmt_comment_text, '2017' as "YEAR", szrspdc_prog_code
from spriden, sarappd, saradap, stvapdc, saracmt a, saracmt c, szrspdc
where blah blah blah

The following three fields are used as a key in another system I am doing an import into so I need to get the following into one field: c.saracmt_comment_text, '2017' as "YEAR", szrspdc_prog_code.

Thanks in advance for your help.

which version of sql? you can use concat from sql 2012, otherwise just do the classic way to concatenating.. 'Hello' + ' World ' — RoMEoMusTDiE
– RoMEoMusTDiE, Commented Mar 28, 2017 at 23:18
You can use || operator ..something like below SELECT firstname || ' ' || lastname AS studentname FROM students; — Janier
– Janier, Commented Mar 28, 2017 at 23:19

cha · Accepted Answer · 2017-03-28 23:20:44Z

1

The string concatenation operator in Oracle is ||. So, you need to use the following query:

select DISTINCT 
c.saracmt_comment_text || '2017' || szrspdc_prog_code as key_field
spriden_pidm, a.saracmt_comment_text, spriden_id, 
c.saracmt_comment_text, '2017' as "YEAR", szrspdc_prog_code 
from spriden, sarappd, saradap, stvapdc, saracmt a, saracmt c, szrspdc 
where blah blah blah

answered Mar 28, 2017 at 23:20

cha

10.4k1 gold badge20 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ero Over a year ago

thank you! I had tried || but didn't have an as field_name at the end

Prescott Chartier · Accepted Answer · 2017-03-28 23:36:42Z

0

Do you want to do this in PL/Sql or on the receiving end is C#/VB.NET Code? I assume PL/Sql.

Procedute SomeName(DataOut OUT Varchar2) IS
    WResult Varchar2(4000) -- I don't know the size of 2 of the table columns involed so I use the max size of Varchar2 which is 4000
Begin

    select into wResult a.saracmt_comment_text || YEAR || szrspdc_prog_code from
    (
      select DISTINCT spriden_pidm, a.saracmt_comment_text, spriden_id,
      c.saracmt_comment_text, '2017' as "YEAR", szrspdc_prog_code from  
      spriden, sarappd, saradap, stvapdc, saracmt a, saracmt c,
      szrspdc_prog_code where Conditions
    );

   DataOut := wResult;
End;

Hope this helps.

R/ Prescott ....

answered Mar 28, 2017 at 23:36

Prescott Chartier

1,6994 gold badges23 silver badges45 bronze badges

Collectives™ on Stack Overflow

Concatenating values from SQL select statement

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related