Concatenate string using +=

Question

I am trying to concatenate some strings in sql. What I am trying to do is something like

string organType = null;
if (liver!=null)  { organType += "LI, "; }
if (kidney !=null) { organType += "KI, "; }
if (intestine != null) { organType += "Intestine"; } 
...

The end result should be organType = LI, KI, Intestine;

Here is my code so far

create or replace PROCEDURE "insertDonInfo"(donNum IN NUMBER, offerDate IN DATE)

IS

organType varchar2(100);
BEGIN

  select case when liver is not null then 'LI' 
              when kidney_r is not null then 'KR'
              when kidney_l is not null then 'KL' 
              when heart is not null then 'HE'
              when liver_domino is not null then 'LI-Dom'
              when lung_r is not null then 'LungR'
              when pancreas is not null then 'PA'
              when liver_split is not null then 'Lsplit'
              when lung_l is not null then 'LungL'
              when intestine is not null then 'Intestine' 
         end                         
from donors
where id = donNum;

...

-------------------------Update---------------------

How do I concatenate the organType to be organType=LI, KR, KL, HE, ... in SQL;

Does this answer your question? [What is the string concatenation operator in Oracle?](https://stackoverflow.com/questions/278189/what-is-the-string-concatenation-operator-in-oracle) — Thilo, Nov 01 '19 at 09:57

score 6 · Accepted Answer · answered Nov 01 '19 at 09:47

sql does not have a += operator. You'll have to check on a per column basis and concatenate. Took a stab at your data structure.

create table so_test (id number primary key, don_name varchar2(100), liver varchar2(1), heart varchar2(1), kidney_r varchar2(1));

insert into so_test (id, don_name, liver, heart, kidney_r) values (1, 'John','Y',NULL,'Y');
insert into so_test (id, don_name, liver, heart, kidney_r) values (2, 'Kathy',NULL,'Y','Y');

SELECT 
  don_name,
  RTRIM(
    CASE WHEN liver IS NOT NULL THEN 'LI, ' ELSE NULL END ||
    CASE WHEN heart IS NOT NULL THEN 'HE, ' ELSE NULL END ||
    CASE WHEN kidney_r IS NOT NULL THEN 'KR, ' ELSE NULL END
  ,', ') as organs
  FROM so_test;

returns

John    LI, KR
Kathy   HE, KR

This is a better answer, because your rtrim/comma removal which my answer didn't have. — WynDiesel, Nov 01 '19 at 10:25

score 3 · Answer 2 · answered Nov 01 '19 at 09:41

I don't have your test data, so it's hard to test this, But try something like this ...

select case when liver is not null then 'LI' end
          || case when when kidney_r is not null then 'KR' end
          || case when kidney_l is not null then 'KL'  end
          || case when heart is not null then 'HE' end
          || case when liver_domino is not null then 'LI-Dom' end
          || case when lung_r is not null then 'LungR' end
          || case when pancreas is not null then 'PA' end
          || case when liver_split is not null then 'Lsplit' end
          || case when lung_l is not null then 'LungL' end
          || case when intestine is not null then 'Intestine'  end
     end                         
from donors
where id = donNum;

score 2 · Answer 3 · answered Nov 01 '19 at 09:52

2

CONCAT function

SELECT CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(liver, kidney_r),kidney_l),heart),liver_domino),lung_r),pancreas),pancreas),liver_split),lung_l),intestine )
FROM donors
WHERE id = donNum;

answered Nov 01 '19 at 09:52

Vignesh Kumar A

27,863
13
63
115

2

`||` is a bit more ergonomical – Thilo Nov 01 '19 at 09:56

Concatenate string using +=

3 Answers3