Formatting string if it's not null

Question

Based on GMB's answer on my previous question I've simplified the query:

SELECT tn.Date, 
case when tn.DrControl = 7 
    then b1.Name || ' -> ' || c1.Name || ' ' || ifnull(l1.Name, '') || ' ' || ifnull(s1.Name, '') || ' ' || ifnull(p1.Name, '') || ' ' || ifnull(m1.Name , '')
    else b2.Name || ' -> ' || c2.Name || ' ' || ifnull(l2.Name, '') || ' ' || ifnull(s2.Name, '' ) || ' ' || ifnull(p2.Name, '') || ' ' || ifnull(m2.Name, '') 
end Particulars,
case when tn.DrControl = 7 then tn.Amount end DrAmount,
case when tn.CrControl = 7 then tn.Amount end CrAmount,
tn.Narration
FROM Transactions tn
LEFT JOIN Books b1 ON b1.Id = tn.DrBook
LEFT JOIN Books b2 ON b2.Id = tn.CrBook
LEFT JOIN ControlLedgers c1 ON c1.Id = tn.DrControl
LEFT JOIN ControlLedgers c2 ON c2.Id = tn.CrControl
LEFT JOIN Ledgers l1 ON l1.Id = tn.DrLedger
LEFT JOIN Ledgers l2 ON l2.Id = tn.CrLedger
LEFT JOIN SubLedgers s1 ON s1.Id = tn.DrSubLedger
LEFT JOIN SubLedgers s2 ON s2.Id = tn.CrSubLedger
LEFT JOIN Parties p1 ON p1.Id = tn.DrParty
LEFT JOIN Parties p2 ON p2.Id = tn.CrParty
LEFT JOIN Members m1 ON m1.Id = tn.DrMember
LEFT JOIN Members m2 ON m2.Id = tn.CrMember
WHERE 7 IN (tn.DrControl, tn.CrControl)

to reduce the overhead of creating a Particulars column in Application code. It produces result like this:

Date        Particulars                             DrAmount     CrAmount     Narration
----------------------------------------------------------------------------------------
2020-06-13  Current Assets -> Cash In Hand Emon     100000       NULL         Some Text

What I want now is to put a -> in between Cash In Hand and Emon, for example, if that column is not null, otherwise empty string.

This is not a complete question, because it assumes any reader is already familiar with your previous question, which may not be the case. — Tim Biegeleisen, Jun 13 '20 at 14:40
@TimBiegeleisen, I've added the hyperlink for those who are not familiar with my previous question. — , Jun 13 '20 at 15:07
Put what is needed from the link directly in your question. Make it self-contained. Please in code questions give a [mre]. (And this code is clearly not minimal.) But this is clearly a basic faq. Please before considering posting read your textbook and/or manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. If you post a question, use one phrasing as title. Reflect your research. See [ask] & the voting arrow mouseover texts. — philipxy, Jun 14 '20 at 00:00

score 0 · Accepted Answer · answered Jun 13 '20 at 14:42

Unfortunately, SQLite does not support concat_ws(), which makes such operation seamless.

An option uses case expressions:

case when tn.DrControl = 7 
then 
    b1.Name 
    || ' -> ' || c1.Name 
    || (case when l1.Name is null then '' else ' -> ' || l1.Name end)
    || (case when s1.Name is null then '' else ' -> ' || s1.Name end )
    || (case when p1.Name is null then '' else ' -> ' || p1.Name end)
    || (case when m1.Name is null then '' else ' -> ' || m1.Name end)
else    
    b2.Name 
    || ' -> ' || c2.Name 
    || (case when l2.Name is null then '' else ' -> ' || l2.Name end)
    || (case when s2.Name is null then '' else ' -> ' || s2.Name end )
    || (case when p2.Name is null then '' else ' -> ' || p2.Name end)
    || (case when m2.Name is null then '' else ' -> ' || m2.Name end)
end Particulars

score 0 · Answer 2 · answered Jun 13 '20 at 14:53

0

If you want to concatenate multiple columns with a separator like '->' and there are null columns then you can use COALESCE() for each of the columns like this:

coalesce(columnn1, '') || coalesce('->' || column2, '') || coalesce('->' || column3, '') || ....

answered Jun 13 '20 at 14:53

forpas

160,666
10
38
76

Is it possible to set both yours and `GMB`'s answers as accepted answers? – Jun 13 '20 at 15:02

Formatting string if it's not null

2 Answers2