0

I have this row that is part of my SQL Select:

REPLACE(STR(temp.AdminTestId,5),' ','0') + '-' + 
REPLACE(STR(A.UserTestId,5),' ','0') + '-' + 
REPLACE(STR(A.Sequence,2),' ','0') as TestId,

When Sequence is null then TestId returns as null. If Sequence is null what I would like is for just the zero padded AdminTestID the "-" and the zero padded UserTestId to appear.

Alan2
  • 23,493
  • 79
  • 256
  • 450

2 Answers2

3

You can use CASE to branch between two scenarios when Sequence is null or not. You can also use ISNULL and COALESCE for null checking

Added code sample, as requested in comments

REPLACE(STR(temp.AdminTestId,5),' ','0') + '-' + 
REPLACE(STR(A.UserTestId,5),' ','0') +  
ISNULL('-' + REPLACE(STR(A.Sequence,2),' ','0'),'') as TestId,
ironstone13
  • 3,325
  • 18
  • 24
1

Concatenating null values to a string will return a null string in Sql server. The solution is usually to use Sql server's built in function to convert null values to empty strings, so your code would look like this:

REPLACE(COALESCE(STR(temp.AdminTestId,5), ''),' ','0') + '-' + 
REPLACE(COALESCE(STR(A.UserTestId,5), ''),' ','0') + '-' + 
REPLACE(COALESCE(STR(A.Sequence,2), ''), ''),' ','0') as TestId,

The COALESCE function is Sql server will return the first argument it receives that is not NULL.

Edit

To answer your comment, taking advantage of the fact that concatenating null to a string will return null, you can simply concatenate the hyphen to A.Sequence:

REPLACE(COALESCE(STR(temp.AdminTestId,5), ''),' ','0') + '-' + 
REPLACE(COALESCE(STR(A.UserTestId,5), ''),' ','0') + 
REPLACE(COALESCE('-' + STR(A.Sequence,2), ''),' ','0') as TestId,
Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
  • thanks. I will try it out. One thing I can see missing here for my example is that if Sequence was null then there would be two hyphens. What I would like is for the second hyphen not to show. Can you give some advice about that also. – Alan2 Mar 06 '16 at 13:43
  • Your last answer seems to have a syntax error. ','0') as TestId, << Incorrect syntax near ). Do you have an idea what might be causing this? I think there's a ( missing but not sure where exactly. – Alan2 Mar 06 '16 at 16:13
  • There was one `, '')` too many. fixed. – Zohar Peled Mar 07 '16 at 06:18