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

Question

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.

What have you tried? You should include some example data and expected result. Also when you say fill with blank characters do you just want CHAR(163) or do you want spaces? — Matt, Sep 22 '16 at 19:27
I've been handling the fillers with Convert(CHAR(163), column) but these are actually 4 columns with many different scenarios, so i'm not sure how to wrap the whole thing. As for the data, i'll try to add more examples. — J_Ocampo, Sep 22 '16 at 19:30
Horrible table design. Have only one mail column. (And use several rows if needed.) — jarlh, Sep 22 '16 at 20:07
As @jarlh said you need to normalize this. Once you have properly normalized data structures the queries become very simple. — Sean Lange, Sep 22 '16 at 20:18
It's a complete and utter chaos. And that's not even the full table, but that's how I recently found this DB. I'll raise the warning and hope we can change it in the future. — J_Ocampo, Sep 22 '16 at 20:24
You can create a view that simulates normalized data (UNION ALL's), and use that view until you've fixed the tables! — jarlh, Sep 22 '16 at 20:34

Edmond Quinton · Answer 1 · 2016-09-22T20:03:39.067

Haven’t tested this but hopefully it works for you.

SELECT ISNULL( Mail1 + ';', '') + ISNULL( Mail2 + ';', '') + ISNULL( Mail3 + ';', '') + ISNULL( Mail4 + ';', '')  AS Result
FROM MailsTable

In the event that your table has empty or blank strings your expression will become a little more complex:

SELECT ISNULL( CASE WHEN RTRIM(Mail1) = '' THEN NULL ELSE Mail1 END + ';', '') + 
        ISNULL( CASE WHEN RTRIM(Mail2) = '' THEN NULL ELSE Mail2 END + ';', '') + 
        ISNULL( CASE WHEN RTRIM(Mail3) = '' THEN NULL ELSE Mail3 END + ';', '') + 
        ISNULL( CASE WHEN RTRIM(Mail4) = '' THEN NULL ELSE Mail4 END + ';', '')  AS Result
FROM MailsTable

I missed the requirement on the string padding. With string padding query will looks as follow:

SELECT  CONVERT(CHAR(163), 
        (
            LEFT(
                    ISNULL( CASE WHEN RTRIM(Mail1) = '' THEN NULL ELSE Mail1 END + ';', '') + 
                    ISNULL( CASE WHEN RTRIM(Mail2) = '' THEN NULL ELSE Mail2 END + ';', '') + 
                    ISNULL( CASE WHEN RTRIM(Mail3) = '' THEN NULL ELSE Mail3 END + ';', '') + 
                    ISNULL( CASE WHEN RTRIM(Mail4) = '' THEN NULL ELSE Mail4 END + ';', '') + SPACE(163), 163)
                ))   AS Result 
FROM MailsTable

score 1 · Accepted Answer · answered Sep 22 '16 at 19:41

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.

Wow, i'm impressed! (didn't even notice the substring getting rid of the leading ; Nice!). Also, im glad you assumed the empy as null or "empty". It was my bad for not being clear about it. Thanks a million! — J_Ocampo, Sep 22 '16 at 20:20

Matt · Answer 3 · 2016-09-22T20:38:32.457

Edit Modified to treat white space, null, and empty strings the same. Note shout out to Joe to remind me to put the semi colon in front. When you do that then you can just use stuff to replace it with a blank character. The Cast to CHAR(163) automatically pads the right side of your string to be 163 characters long.

SELECT
    *
    ,CAST(
       STUFF(
          CASE WHEN COALESCE(RTRIM(Mail1),'') <> '' THEN ';' + Mail1 ELSE '' END
          + CASE WHEN COALESCE(RTRIM(Mail2),'') <> '' THEN ';' + Mail2 ELSE '' END
          + CASE WHEN COALESCE(RTRIM(Mail3),'') <> '' THEN ';' + Mail3 ELSE '' END
          + CASE WHEN COALESCE(RTRIM(Mail4),'') <> '' THEN ';' + Mail4 ELSE '' END
       ,1,1,'')
    AS CHAR(163))
FROM
    @MailsTable
WHERE
    COALESCE(RTRIM(Mail1),'') <> ''
    OR COALESCE(RTRIM(Mail2),'') <> ''
    OR COALESCE(RTRIM(Mail3),'') <> ''
    OR COALESCE(RTRIM(Mail4),'') <> ''

Here is the test data:

DECLARE @MailsTable AS TABLE (Id INT IDENTITY(1,1), Mail1 VARCHAR(40), Mail2 VARCHAR(40), Mail3 VARCHAR(40), Mail4 VARCHAR(40))
INSERT INTO @MailsTable VALUES
('Mail1@M1.Com','Mail2@M2.Com','Mail3@M3.com','Mail4@M4.com')
,(NULL,'Mail2@M2.Com','Mail3@M3.com','Mail4@M4.com')
,('Mail1@M1.Com',NULL,'Mail3@M3.com','Mail4@M4.com')
,('Mail1@M1.Com','Mail2@M2.Com',NULL,'Mail4@M4.com')
,('Mail1@M1.Com','Mail2@M2.Com','Mail3@M3.com',NULL)
,('Mail1@M1.Com',NULL,NULL,NULL)
,('Mail1@M1.Com','Mail2@M2.Com',NULL,NULL)
,(NULL,'Mail2@M2.Com',NULL,'Mail4@M4.com')
,(NULL,'Mail2@M2.Com',NULL,NULL)
,('Mail1@M1.Com',NULL,NULL,'Mail4@M4.com')
,(NULL,NULL,NULL,'Mail4@M4.com')
,(NULL,NULL,NULL,NULL)
,(NULL,NULL,NULL,'')
,('Mail1@M1.Com','',NULL,'Mail4@M4.com')
,(' ',NULL,'   ','Mail4@M4.com')
,(NULL,NULL,NULL,'   ')

Thanks Matt! I failed to clarify that by empty i meant both null and an empty string (so it wasn't working for me 100% given my data). Still thanks a lot for the help. Made+saved my day :D — J_Ocampo, Sep 22 '16 at 20:22
@J_Ocampo yeah I guess my solution had only partially dealt with the empty string. I changed it up to be able to handle empty white space (crlf, tab, spaces), nulls, and empty string. By stealing joes idea to preface the address with the ; then you can use stuff and get rid of the leading character makes it more tidy than having to use another cte or copying the log concatenation statement.... — Matt, Sep 22 '16 at 20:41

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

3 Answers3