-3

I would like to not have a comma when there is a no value after string but I keep getting back a comma even if there is a value

enter image description here

  select 
                company_no,
                company_name, 
                concat(address_1 
                         ,CASE WHEN NULLIF(address_1,'') <> ''  THEN ', ' 
                               ELSE '' 
                          END) + concat(address_2
                         ,CASE WHEN NULLIF(address_2,'')<> ''  THEN ', ' 
                               ELSE '' 
                          END) + concat(address_3
                         ,CASE WHEN NULLIF(address_3,'')<> '' THEN ', ' 

                               ELSE '' 
                          END)+
                           concat(address_4
                         ,CASE WHEN NULLIF(address_4,'')<> ''  THEN ', ' 
                               ELSE '' 
                          END)+
                           concat(address_5
                         ,CASE WHEN NULLIF(address_5,'')<> ''  THEN ' ' 
                               ELSE '' 
                          END) as Address,
                code.code_desc as company_status

                from company_tbl company
                inner join code_tbl code on company.company_status = code.code and code.code_type = 'COM' and code_sub_type = 'CIR'


                where company_name like company_name +'%'
                and company_type = 'EOC'
                and address_3 is null

What I am getting :

  1. 3050 LakedTest,, CityTest
  2. TBC,
  3. NULL

Desired output

  1. 3050 LakedTest,CityTest
  2. TBC
  3. (Want this just to be blank)

Update : I had to add a the REPLACE function to get rid of commas that were in the String values.

4

Use STUFF function, or if your server version is 2017 or higher just use CONCAT_WS

DECLARE @Test TABLE (
    address_1   VARCHAR(10),
    address_2   VARCHAR(10),
    address_3   VARCHAR(10),
    address_4   VARCHAR(10),
    address_5   VARCHAR(10)
)

INSERT @Test
VALUES
('AAA', 'BBB', 'CCC', 'DDD', 'EEE'),
(NULL, 'BBB', 'CCC', 'DDD', 'EEE'),
(NULL, NULL, NULL, 'DDD', 'EEE'),
(NULL, NULL, NULL, NULL, NULL),
(NULL, NULL, NULL, NULL, 'EEE'),
('AAA', NULL, NULL, NULL, 'EEE'),
('AAA', NULL, NULL, NULL, NULL)

SELECT 
ISNULL(
    STUFF(
        CONCAT ( 
            ', ' + NULLIF(REPLACE(address_1, ',', ''), ''),
            ', ' + NULLIF(REPLACE(address_2, ',', ''), ''),
            ', ' + NULLIF(REPLACE(address_3, ',', ''), ''),
            ', ' + NULLIF(REPLACE(address_4, ',', ''), ''),
            ', ' + NULLIF(REPLACE(address_5, ',', ''), '')
        ),
        1,
        2,
        ''
    ),
    ''
)
FROM @Test
0
select company_no,
       company_name, 
       COALESCE(concat_ws(', ',
                          NULLIF(address_1,''),
                          NULLIF(address_2,''),
                          NULLIF(address_3,''),
                          NULLIF(address_4,''),
                          NULLIF(address_5,'')), '') as Address,
       code.code_desc as company_status

from company_tbl company
inner join code_tbl code on company.company_status = code.code 
                        and code.code_type = 'COM' 
                        and code_sub_type = 'CIR'

where company_name like company_name +'%' -- ???
  and company_type = 'EOC'
  and address_3 is null -- ???

company_name like company_name +'%' is extremely strange - it is always true except the case when company_name is NULL.

and address_3 is null is strange too - more precise it is strange why this field is nevertheless included into output expression.

  • This part company_name like company_name +'%', this is used to populate a repeating table in an application, so you if type for e.g Intel, it will get all the companies like intel and populate into a table on the application side. – Rachel Nov 19 '19 at 15:38
  • @Rachel ??? It may be true in a form similar to @company_name like company_name +'%', where @company_name is a string parameter transferred to a query by code. In shown expression it is a field value, moreover, the value of a field in the same record - so it makes no sense. – Akina Nov 19 '19 at 17:15
0

Is this due to comparing NULL values? For instance, instead of saying

CASE WHEN NULLIF(address_2,'') <> '' THEN ', ' ELSE '' END

Would you rather say

CASE WHEN ISNULL(NULLIF(address_2,''),'1') <> '1' THEN ', ' ELSE '' END

Using ISNULL avoids the issue of comparing NULL values since you're now setting the expression equal to 1 if it's NULL, then comparing 1 to 1. SQL does not know how to compare NULL to NULL, and so any statement comparing NULLs will always evaluate to TRUE.

Edit: had the wrong comparison operator in the second statement, should be <>.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.