How to display empty when value of paramters is null?

Question

I work on SQL server 2012 the value

 DECLARE @Company_ID VARCHAR(450)='1006274'  
    DECLARE @PackageDrawingNumber VARCHAR(450)=NULL  
    DECLARE @PartOrderSuffix VARCHAR(450)='N/A'  
    DECLARE @CountOfPCBConnectedPin VARCHAR(450)='N/A'  
  
      
    DECLARE @DKDrawingNumber VARCHAR(MAX);  
    IF @PartOrderSuffix='N/A'  
    SET @PartOrderSuffix=''  
    IF @CountOfPCBConnectedPin='N/A'  
    SET @CountOfPCBConnectedPin=''  

SET @DKDrawingNumber=isnull(@mathieu.company _ID,' ')+isnull('~ '+isnull(@PackageDrawingNumber,''),'') + isnull('~ '+@PartOrderSuffix,'')+isnull('~ '+@CountOfPCBConnectedPin,'')
SELECT @DKDrawingNumber

desired result
1006274

but it display wrong result as below
1006274~ ~ ~

i need when parameters is null not display ~
so how to do that

Answer 1

DECLARE @mathieu.company _ID VARCHAR(450)='1006274'
DECLARE @PackageDrawingNumber VARCHAR(450)=NULL
DECLARE @PartOrderSuffix VARCHAR(450)='N/A'
DECLARE @CountOfPCBConnectedPin VARCHAR(450)='N/A'

 DECLARE @DKDrawingNumber VARCHAR(MAX);  
 IF @PartOrderSuffix='N/A'  
 SET @PartOrderSuffix=''  
 IF @CountOfPCBConnectedPin='N/A'  

SET @DKDrawingNumber = isnull( @mathieu.company _ID, ' ')
SET @DKDrawingNumber = @DKDrawingNumber
+ IIF( ISNULL(@PackageDrawingNumber, '') = '', '', '~ ' + @PackageDrawingNumber)
+ IIF( ISNULL(@PartOrderSuffix, '') = '', '', '~ ' + @PartOrderSuffix)
+ IIF( ISNULL(@CountOfPCBConnectedPin, '') = '', '', '~ ' + @CountOfPCBConnectedPin)

-- + isnull('~ '+isnull(@PackageDrawingNumber,''),'') + isnull('~ '+@PartOrderSuffix,'')
SELECT @DKDrawingNumber

Answer 2

If CONCAT is available and @mathieu.company _ID is never null, then try this approach too:

set @DKDrawingNumber = CONCAT(  
    @Company_ID,   
    '~' + @PackageDrawingNumber,   
    '~' + nullif(@PartOrderSuffix, 'N/A'),   
    '~' + nullif(@CountOfPCBConnectedPin, 'N/A'))  

Answer 3

Use ISNULL() and NULLIF(). Concat the values with the spacer before testing for NULL. And the last trick is to add the spacer before the value, Then you can use STUFF() to remove the first spacer (assumption at least on part has a value and you run CONCAT_NULL_YIELDS_NULL ). E.g.

DECLARE @Company_ID VARCHAR(450) = '1006274';  
DECLARE @PackageDrawingNumber VARCHAR(450) = NULL;  
DECLARE @PartOrderSuffix VARCHAR(450) = 'N/A';  
DECLARE @CountOfPCBConnectedPin VARCHAR(450) = 'N/A';  
  
SELECT ISNULL(@Company_ID, ' ') +   
	   ISNULL('~ ' + ISNULL(@PackageDrawingNumber, ''), '') +   
	   ISNULL('~ ' + @PartOrderSuffix, '') +   
	   ISNULL('~ ' + @CountOfPCBConnectedPin, '');  
  
SELECT STUFF(  
           ISNULL('-' + @Company_ID, '') +   
		   ISNULL('-' + @PackageDrawingNumber, '') +   
		   ISNULL('-' + NULLIF(@PartOrderSuffix, 'N/A'), '') +   
		   ISNULL('-' + NULLIF(@CountOfPCBConnectedPin, 'N/A'), '') ,  
           1 , 1 , '');  

Answer 4

Hi @ahmed salah ，

Please refer to:

DECLARE @Company_ID VARCHAR(450)='1006274'  
DECLARE @PackageDrawingNumber VARCHAR(450)=null  
DECLARE @PartOrderSuffix VARCHAR(450)='N/A'  
DECLARE @CountOfPCBConnectedPin VARCHAR(450)='N/A'  
                          
DECLARE @DKDrawingNumber VARCHAR(MAX);  
IF @PartOrderSuffix='N/A'  
SET @PartOrderSuffix=''  
IF @CountOfPCBConnectedPin='N/A'  
SET @CountOfPCBConnectedPin=''  
SET @DKDrawingNumber=isnull(@Company_ID,' ')  
      +IIF(@PackageDrawingNumber is not null and @PackageDrawingNumber<>'','~ '+@PackageDrawingNumber ,'')  
      +IIF(@PartOrderSuffix is not null and @PartOrderSuffix<>'','~ '+@PartOrderSuffix,'')  
      +IIF(@CountOfPCBConnectedPin is not null and @CountOfPCBConnectedPin<>'','~ '+@CountOfPCBConnectedPin ,'')  
SELECT @DKDrawingNumber  

For more details, please refer to:
Logical Functions - IIF (Transact-SQL)

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.