CODEWITHSUNDEEP
sqloracle-database
user1960836
user1960836

Reputation: 1782

Concatenate string using +=

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;

Upvotes: 2

Views: 151

Answers (3)

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28413

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;

Upvotes: 2

Koen Lostrie
Koen Lostrie

Reputation: 18685

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

Upvotes: 6

WynDiesel
WynDiesel

Reputation: 1214

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;

Upvotes: 3

Related Questions