CODEWITHSUNDEEP
sqlsql-servert-sql
akhrot
akhrot

Reputation: 445

Inserting an optional semicolon between two selected fields

This is my SQL clause

ISNULL((LTRIM(RTRIM(Masters.comment1))+';'+LTRIM(RTRIM(masters.comment2))),'')Note1 ,

When there are no value in both the column, then I am getting only a semicolon. If the value is not in comment1 then I am getting ;xyz.

I want that:

Upvotes: 0

Views: 124

Answers (3)

artm
artm

Reputation: 8584

If either is null use blank space, else ';'. If either is null coalesce will return NULL otherwise 'eithernull'

LTRIM(RTRIM(Masters.comment1))
+ CASE WHEN (coalesce(@a + @b, 'eithernull')) = 'eithernull' THEN '' ELSE ';' END 
+ LTRIM(RTRIM(masters.comment2))

Upvotes: 0

t-clausen.dk
t-clausen.dk

Reputation: 44346

This will add the semicolon when there is data is both columns

SELECT 
  COALESCE(LTRIM(RTRIM(comment1)),'') + 
  CASE WHEN NULLIF(comment1, '') + NULLIF(comment2, '')IS NULL THEN '' ELSE ';' END +
  COALESCE(LTRIM(RTRIM(comment2)),'')
FROM yourtable

Upvotes: 2

Y.B.
Y.B.

Reputation: 3596

You can use NULLIF to turn empty string to NULL. Then concatenation of NULL with ';' would still be NULL and that can be turned into and empty string with ISNULL:

WITH masters (comment1, comment2) AS (
    SELECT NULL, NULL UNION ALL
    SELECT ' 1', NULL UNION ALL
    SELECT NULL, '2 ' UNION ALL
    SELECT '3 ', ' 4' UNION ALL
    SELECT ''  , ''   UNION ALL
    SELECT ' 5', ''   UNION ALL
    SELECT ''  , '6 ' UNION ALL
    SELECT '7 ', ' 8'
)
SELECT
    ISNULL(
        (
            ISNULL(NULLIF(LTRIM(RTRIM(masters.comment1)), '') + ';', '')
            + NULLIF(LTRIM(RTRIM(masters.comment2)), '')
        )
        , ISNULL(LTRIM(RTRIM(masters.comment1)), '')) Note1
FROM masters;

Update: Jorge Campos has a nice and very easy to read solution using CASE:

WITH masters (comment1, comment2) AS (
    SELECT NULL, NULL UNION ALL
    SELECT ' 1', NULL UNION ALL
    SELECT NULL, '2 ' UNION ALL
    SELECT '3 ', ' 4' UNION ALL
    SELECT ''  , ''   UNION ALL
    SELECT ' 5', ''   UNION ALL
    SELECT ''  , '6 ' UNION ALL
    SELECT '7 ', ' 8'
)
SELECT        ISNULL(LTRIM(RTRIM(masters.comment1)), '') + 

    CASE WHEN ISNULL(LTRIM(RTRIM(masters.comment1)), '') <> ''
          AND ISNULL(LTRIM(RTRIM(masters.comment2)), '') <> ''
         THEN ';'
         ELSE ''
    END +  
              ISNULL(LTRIM(RTRIM(masters.comment2)),'') AS Note1 
FROM masters;

Upvotes: 4

Related Questions