Skip to content
Advertisement

Set prefix in SQL CASE expression

I have problem setting a prefix in a case statement.

Data set:

missionid:
5505
5506


select
CASE
WHEN m.EXTLOCATIONID is not null THEN '01' + convert(nvarchar(50),m.missionid) 
ELSE tg.ID_ACTIVITY
END as Barcode2
from MISSION m
left join TASKGROUP tg with(nolock) on m.MMPICKLISTID = tg.ID

When I run this query my result is this:

Barcode2: 
15505
15506

Desired result is this:

015505
015506

As one can see, the first zero is not shown in the result. How can I achieve this?

Advertisement

Answer

CASE expression would always return one type so, you need to do conversion:

CASE WHEN m.EXTLOCATIONID is not null 
     THEN '01' + convert(nvarchar(50), m.missionid) 
     ELSE CONVERT(VARCHAR(255), tg.ID_ACTIVITY)
END as Barcode2
User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement