J_Ocampo J_Ocampo - 2 months ago 5
SQL Question

SQL Server: SELECT 4 non-empty columns and concatenate them

Let's say i have the following setup

MailsTable
Mail1 Varchar(40)
Mail2 Varchar(40)
Mail3 Varchar(40)
Mail4 Varchar(40)


Now i'm building a Query that shall include rows that have at least 1 Mail that isn't empty (Mail1 or Mail2...etc). And concatenate all non-empty mails with ';'
And then fill the missing blank characters to reach 163 characters total (all 4 mails + ;)

Example:

1) In a row where Mail1 and Mail3 exist:

george@net.com;louis@net.com

(and 163-28=135 blank characters to fill 163 total characters)

2) In a row where Mail2, Mail3 and Mail4 exist:

mail2@gmail.com;mail3@gmail.com;mail4@gmail.com (and many blank characters to fill 163)

But it gets a bit tricky when i try to build the Select, i thought about using the CASE WHEN THEN clauses but end up having too many cases to handle. Along with the "fill with blank characters" issue.

Any help is welcome.
Thanks in advance.

Answer

I don't really get why you want to pad these up to 163 characters, but here's an example that shows all possible combinations of empty and non-empty addresses:

declare @MailsTable table
(
    RowNumber int,
    Mail1 varchar(40),
    Mail2 varchar(40),
    Mail3 varchar(40),
    Mail4 varchar(40)
);

insert @MailsTable values
    (0, null, null, null, null),
    (1, 'Addr1', null, null, null),
    (2, null, 'Addr2', null, null),
    (3, 'Addr1', 'Addr2', null, null),
    (4, null, null, 'Addr3', null),
    (5, 'Addr1', null, 'Addr3', null),
    (6, null, 'Addr2', 'Addr3', null),
    (7, 'Addr1', 'Addr2', 'Addr3', null),
    (8, null, null, null, 'Addr4'),
    (9, 'Addr1', null, null, 'Addr4'),
    (10, null, 'Addr2', null, 'Addr4'),
    (11, 'Addr1', 'Addr2', null, 'Addr4'),
    (12, null, null, 'Addr3', 'Addr4'),
    (13, 'Addr1', null, 'Addr3', 'Addr4'),
    (14, null, 'Addr2', 'Addr3', 'Addr4'),
    (15, 'Addr1', 'Addr2', 'Addr3', 'Addr4');

with ConcatenatedAddressesCTE as
(
    select
        RowNumber,
        Mails =
            case when Mail1 != '' then ';' + Mail1 else '' end +
            case when Mail2 != '' then ';' + Mail2 else '' end +
            case when Mail3 != '' then ';' + Mail3 else '' end +
            case when Mail4 != '' then ';' + Mail4 else '' end
    from
        @MailsTable
    where
        Mail1 != '' or Mail2 != '' or Mail3 != '' or Mail4 != ''
)
select
    RowNumber,
    FormattedMails = substring(Mails, 2, 200) + replicate(' ', 164 - len(Mails))
from
    ConcatenatedAddressesCTE;

Results:

RowNumber  FormattedMails
1          Addr1                                                                                                                                                              
2          Addr2                                                                                                                                                              
3          Addr1;Addr2                                                                                                                                                        
4          Addr3                                                                                                                                                              
5          Addr1;Addr3                                                                                                                                                        
6          Addr2;Addr3                                                                                                                                                        
7          Addr1;Addr2;Addr3                                                                                                                                                  
8          Addr4                                                                                                                                                              
9          Addr1;Addr4                                                                                                                                                        
10         Addr2;Addr4                                                                                                                                                        
11         Addr1;Addr2;Addr4                                                                                                                                                  
12         Addr3;Addr4                                                                                                                                                        
13         Addr1;Addr3;Addr4                                                                                                                                                  
14         Addr2;Addr3;Addr4                                                                                                                                                  
15         Addr1;Addr2;Addr3;Addr4                                                                                                                                            

The CTE prefixes every non-empty address with a semicolon so that it doesn't have to worry about how many empty addresses might occur between two non-empty addresses. This means that every result it produces will start with a semicolon, which is why the final part of the query strips off the leftmost character, and why it uses 164 rather than 163 in the length calculation.

I have assumed here that when you say an "empty" mail, you could mean either a null or an empty string.

Comments