I have a two fields name_line1 and name_line2 in database.

In the input form it is one field “name” but it is operated on by substr to get two VARCHAR(30) and assigned to the above two fields.

I would like to concatenate both fields together. But in the database, there is no trailing whitespaces for name_line1. I cannot use the following code from this link Add SPACE to CONCAT with SUBSTR Oracle SQL because there are cases where the point to substr is in the middle of a word (at n in Stationaries below):

                                 HERE
    Experimental unit 1998 Station|aries Sdn Bhd.
          name_line1                    name_line2

How do I write a code to conditionally concatenate white space if name_line1 is not 30 characters and name_line2 has characters or any other more relevant condition?

CodePudding user response：

Maybe this is what you need:

WITH sample AS
    (SELECT 1 "SAMPLE", 'Experimental unit 1998' "F1", 'Stationaries Sdn Bhd.' "F2" FROM Dual UNION ALL
     SELECT 2 "SAMPLE", 'Experimental unit 1998 Stationaries ' "F1", ' Sdn Bhd.' "F2" FROM Dual UNION ALL
     SELECT 3 "SAMPLE", 'Experimental unit 1998 Stationaries Sdn Bhd.' "F1", Null "F2" FROM Dual UNION ALL
     SELECT 4 "SAMPLE", Null "F1", Null "F2" FROM Dual UNION ALL
     SELECT 5 "SAMPLE", Null "F1", 'Experimental unit 1998 Stationaries Sdn Bhd.' "F2" FROM Dual)
SELECT 
  F1, F2,
    CASE 
      WHEN F1 Is Null And F2 Is Not Null THEN F2
      WHEN F1 Is Not Null And F2 Is Null THEN F1
      WHEN Length(RTRIM(F1)) <= 30 And Length(LTRIM(F2)) > 0 THEN F1 || ' ' || LTRIM(F2)
      WHEN Length(RTRIM(F1)) > 30 And Length(LTRIM(F2)) > 0 THEN F1 || ' ' || LTRIM(F2)
  ELSE F1
    END "THE_RESULT"
FROM 
    sample 
--
--  R e s u l t
--
--  F1                                           F2                                                 THE_RESULT                                                                              
--  -------------------------------------------- -------------------------------------------------- -----------------------------------------------
--  Experimental unit 1998                       Stationaries Sdn Bhd.                              Experimental unit 1998 Stationaries Sdn Bhd.   
--  Experimental unit 1998 Stationaries           Sdn Bhd.                                          Experimental unit 1998 Stationaries Sdn Bhd.  
--  Experimental unit 1998 Stationaries Sdn Bhd. NULL                                               Experimental unit 1998 Stationaries Sdn Bhd.   
--  NULL                                         NULL                                               NULL                                             
--  NULL                                         Experimental unit 1998 Stationaries Sdn Bhd.       Experimental unit 1998 Stationaries Sdn Bhd.

As you can see, using CASE with multiple conditions and intentionally removing spaces where they could be present (RTRIM / LTRIM) gives you opportunity to put a space where you want it to be. Also the result could be or should be a subject of TRIM.

CodePudding user response：

Alright, so based on @JNevill's comment, I have used the following code:

Select vendor_id, 
    CASE 
         WHEN LENGTH(name_line1) <30 AND name_line2 IS NOT NULL THEN name_line1||' '||name_line2  
         ELSE name_line1||name_line2 
    END AS FullName 
from ap_vendors where vendor_id like '%YOULINE%';

The additional cases from @dr was helpful in considering if I missed out any potential loopholes, but after some consideration I decided that since any leading whitespaces in name_line2 would be intentional, its better to keep it as is.

The code should cover cases

1. where trailing whitespaces is removed from name_line1 by adding a whitespace,
2. covers substr in the middle of a word by concatenating without adding whitespace,
3. covers substr at end of word while name_line2 has leading whitespace, by concatenating as is without adding whitespace.