locked
Business rule to concatenate columns with Hyphens in between RRS feed

  • Question

  • Hi,


    I want to create a business rule to concatenate multiple columns , with a hyphen in between - Ex: If the column1 is 'Asia' and column2 is  'India' and Column3 is 'MadhyaPradesh' and column4 is 'MuzzafarNagar', then it should populate Asia-India-MadhyaPradesh-MuzzafarNagar. I created a business rule like , concatenate columns -> Column 1+ '-'+ Column 2+ '-'+Column 3+ '-'+Column 4. Its working for these kind of values

    Now, the issue is, NOT all the columns have values. For example, if the column 2 and column 3 values are blank, then I am getting the values as Asia- - -. In this case, if the column value is blank, then i don't want the column.   Please help me how to apply this rule for these kind of scenarios?

    Monday, February 18, 2019 6:16 PM

Answers

  • DECLARE @T TABLE (
    	Col1 varchar(20),
    	Col2 varchar(20),
    	Col3 varchar(20),
    	Col4 varchar(20),
    	Col5 AS (
    		CASE 
    			WHEN Col2 IS NULL THEN Col1 
    			WHEN Col3 IS NULL THEN CONCAT(Col1, '-' + Col2) 
    			WHEN Col4 IS NULL THEN CONCAT(Col1, '-' + Col2, '-' + Col3) 
    			ELSE CONCAT(Col1, '-' + Col2, '-' + Col3, '-' + Col4) 
    		END
    	)
    );
    INSERT INTO @T VALUES
    ('Asia', 'India', 'MadhyaPradesh', 'MuzzafarNagar'),
    ('Asia', 'India', 'MadhyaPradesh', NULL),
    ('Asia', 'India', NULL, ''),
    ('Asia', NULL, '', '');
    
    SELECT * FROM @T;


    A Fan of SSIS, SSRS and SSAS

    • Marked as answer by Manesar Monday, February 18, 2019 9:34 PM
    Monday, February 18, 2019 8:09 PM

All replies

  • Are you saying this:

    Column1   Column2   Column3             Column4           NewColumn

    Asia          India        MadhyaPradesh   MuzzafarNagar   Asia-India-MadhyaPradesh-MuzzafarNagar

    Asia                                                   MuzzafarNagar   


    A Fan of SSIS, SSRS and SSAS

    Monday, February 18, 2019 7:36 PM
  • In my data set, there are three main things:
    column1 is compulsory.Others are NOT. They may be blank.
    Columns 1,2,3,4,5 are in hiearchy format. So,If column 2 is null, then all the column 3,4 5 are blank.
    If column 1,2 has values and column 3 is null, then all the column 4 , 5 are blank.So, basically if a column has Blank value then other consequent columns are null.

    So, Asia          India        MadhyaPradesh   MuzzafarNagar  is possible. 
    Similarly  Asia          India        MadhyaPradesh is possible.  Asia          India        is possible. But 
    Asia          MuzzafarNagar is not possible.

    Now, if i apply the rule, i am getting like
    Asia-India-MadhyaPradesh-MuzzafarNagar
    Asia-India-MadhyaPradesh-
    Asia-India--
    Asia----

    I want like the below instead of the above

    Asia-India-MadhyaPradesh-MuzzafarNagar
    Asia-India-MadhyaPradesh
    Asia-India
    Asia
    Monday, February 18, 2019 7:56 PM
  • DECLARE @T TABLE (
    	Col1 varchar(20),
    	Col2 varchar(20),
    	Col3 varchar(20),
    	Col4 varchar(20),
    	Col5 AS (
    		CASE 
    			WHEN Col2 IS NULL THEN Col1 
    			WHEN Col3 IS NULL THEN CONCAT(Col1, '-' + Col2) 
    			WHEN Col4 IS NULL THEN CONCAT(Col1, '-' + Col2, '-' + Col3) 
    			ELSE CONCAT(Col1, '-' + Col2, '-' + Col3, '-' + Col4) 
    		END
    	)
    );
    INSERT INTO @T VALUES
    ('Asia', 'India', 'MadhyaPradesh', 'MuzzafarNagar'),
    ('Asia', 'India', 'MadhyaPradesh', NULL),
    ('Asia', 'India', NULL, ''),
    ('Asia', NULL, '', '');
    
    SELECT * FROM @T;


    A Fan of SSIS, SSRS and SSAS

    • Marked as answer by Manesar Monday, February 18, 2019 9:34 PM
    Monday, February 18, 2019 8:09 PM
  • Thanks - I want this column to be populated as Business rule only because Users may change the value of the columns 2,3,4,5 anytime and hence business rule would be better.Thanks in advance!
    Monday, February 18, 2019 8:22 PM
  • thanks - i got it -  i created 4 separate business rules. thanks - pls ignore this!!
    Monday, February 18, 2019 9:34 PM