CODEWITHSUNDEEP
sql-server
Yvette Thiart
Yvette Thiart

Reputation: 1

Concatenating two columns where one contains nulls

I have a view on SQL 2012 where I am trying to concatenate 2 nvarchar columns. It returns nulls if one of the values are null.

I have tried the concat function, I have tried SET CONCAT_NULL_YIELDS_NULL OFF I have tried to use isnull(column name) but still it is returning nulls

Here is my query:

SELECT TOP (100) PERCENT Assignment
                        ,[Document Number]+[Dunning Block] AS [Document Number]
                        ,[Document Type]
                        ,[Document Date]
                        ,[Special G/L Indicator]
                        ,[Amount in local currency]
                        ,[Local Currency]
                        ,[Clearing Document]
                        ,Text
                        ,Account
                        ,Reference
                        ,[Dunning block]
                        ,[Invoice reference]
                        ,SUBSTRING(strDoc_Date, 1, 4) AS Year
                        ,CASE WHEN SUBSTRING(strDoc_Date, 1, 4) = '2013' 
                            THEN CASE WHEN SUBSTRING(strDoc_Date, 6, 2) = '01' 
                                THEN CASE WHEN CAST(SUBSTRING(strDoc_Date, 9, 2) AS integer) > 25 THEN '02' 
                                        ELSE SUBSTRING(strDoc_Date, 6, 2) END 
                                ELSE SUBSTRING(strDoc_Date, 6, 2) END
                            ELSE SUBSTRING(strDoc_Date, 6, 2) END AS Period
                        , SUBSTRING(strDoc_Date, 9, 2) AS Day
FROM dbo.Zcustage

Any suggestions would be appreciated.

Upvotes: 0

Views: 48

Answers (3)

Vicky_Burnwal
Vicky_Burnwal

Reputation: 981

You need ISNULL

ISNULL(YourNullableField,'')

Upvotes: 0

Bridge
Bridge

Reputation: 30721

SET CONCAT_NULL_YIELDS_NULL OFF would only work for concatenating values and NULL- if all values are NULL, you'll still get NULL. I suspect both columns have NULL values in, despite your question title - any of your approaches probably would have worked if you had just inspected the data.

ISNULL should work fine for your situation, but make sure you do it on both columns involved that could be nullable:

SELECT TOP (100) PERCENT Assignment,
                         ISNULL([Document Number],'') + ISNULL([Dunning Block], '') AS [Document Number],
                         [Document Type],
                         [Document Date],
                         [Special G/L Indicator],
                         [Amount in local currency],
                         [Local Currency],
                         [Clearing Document],
                         Text,
                         Account,
                         Reference,
                         [Dunning block],
                         [Invoice reference],
                         SUBSTRING(strDoc_Date, 1, 4) AS Year,
                         CASE
                           WHEN SUBSTRING(strDoc_Date, 1, 4) = '2013' THEN
                             CASE
                               WHEN SUBSTRING(strDoc_Date, 6, 2) = '01' THEN
                                 CASE
                                   WHEN CAST(SUBSTRING(strDoc_Date, 9, 2) AS INTEGER) > 25 THEN '02'
                                   ELSE SUBSTRING(strDoc_Date, 6, 2)
                                 END
                               ELSE SUBSTRING(strDoc_Date, 6, 2)
                             END
                           ELSE SUBSTRING(strDoc_Date, 6, 2)
                         END AS Period,
                         SUBSTRING(strDoc_Date, 9, 2) AS Day
FROM   dbo.Zcustage

On a side note, using TOP 100 PERCENT here is a no-op - it doesn't do anything.

Upvotes: 0

TheGameiswar
TheGameiswar

Reputation: 28938

Use CONCAT

CONCAT[Document Number],[Dunning Block])

CONCAT Ignores nulls

CONCAT takes a variable number of string arguments and concatenates them into a single string. It requires a minimum of two input values; otherwise, an error is raised. All arguments are implicitly converted to string types and then concatenated. Null values are implicitly converted to an empty strin

Upvotes: 1

Related Questions