August 7, 2016 at 8:39 am
following is the sql code, when i execute leading zeros are missing in case statement, is there are alternate way to get leading zeros
DROP TABLE #test;
CREATE TABLE #test (
Col1 int, StatusFlag char(1),col2 CHAR(10)
);
INSERT INTO #test
SELECT '12434', 'P', '0000012344' UNION ALL
SELECT '2','R','0000000002' UNION ALL
SELECT '23231425','R','0023231425' UNION ALL
SELECT '232313','R' ,'0000232313';
select col1,col2,StatusFlag,case when StatusFlag = 'R' then col2 else Col1 end
from #test
August 7, 2016 at 8:59 am
This is because of an implicit conversion from CHAR to INT, simply add an explicit conversion on the INT column in the case statement.
😎
DROP TABLE #test;
CREATE TABLE #test (
Col1 int, StatusFlag char(1),col2 CHAR(10)
);
INSERT INTO #test
SELECT '12434', 'P', '0000012344' UNION ALL
SELECT '2','R','0000000002' UNION ALL
SELECT '23231425','R','0023231425' UNION ALL
SELECT '232313','R' ,'0000232313';
select
col1
,col2
,StatusFlag
,case
when StatusFlag = 'R' then col2
else CONVERT(CHAR(10),Col1,0)
end AS CASE_COL
from #test;
Output
col1 col2 StatusFlag CASE_COL
----------- ---------- ---------- ----------
12434 0000012344 P 12434
2 0000000002 R 0000000002
23231425 0023231425 R 0023231425
232313 0000232313 R 0000232313
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply