CODEWITHSUNDEEP
sql-serverstored-procedures
noleavename
noleavename

Reputation: 83

Prevent removing leading zero when using case when and substring in SQL Server

I have a variable like this

DECLARE @CODE NVARCHAR(10) = '09000:09502';

I need to split the @CODE variable into two variables and insert them into a temporary table for doing bulk insert.

But when I using 'CASE WHEN' inside my select statement, the value after split will remove the leading zero, '09000' will became '9000' (seem like those value is treat as number instead of string). Even I using CAST function, it is still not working.

I doing a little testing using this code:

DECLARE @CODE   VARCHAR(10) = '09000:09502';
DECLARE @Split1 VARCHAR(10) = '';
DECLARE @Split2 VARCHAR(10) = '';

SET @Split1 = CAST((CASE WHEN CHARINDEX(':', @CODE) > 0 
                            THEN LEFT(@CODE, CHARINDEX(':', @CODE) - 1)
                            ELSE -1 
                    END) AS VARCHAR(20))
SET @Split2 = LEFT(@CODE, 5);

PRINT(@Split1);
PRINT(@Split2);

The output I get is:

9000
09000

When I didn't using 'CASE WHEN', I can get the value what I want to, but I using 'CASE WHEN', the leading zero will be removed.

How can I preserve the leading zero value when I use CASE WHEN and LEFT function in a SQL Server stored procedure?

Thanks in advance.

Upvotes: 2

Views: 1194

Answers (4)

Mark Schultheiss
Mark Schultheiss

Reputation: 34217

You should probably just make your conversion impervious to the values, Here are some test cases: The second query is the one you can use with just the two columns, INSERT or UPDATE as you see fit

DECLARE @Codes TABLE (Code VARCHAR(20));
INSERT INTO @Codes (Code)
Values('09000:09502'),('090001:09502'),('090001:095022'),('0900022:09502'),
('090001:095021'),('0900055'),(':0900056'),('090003:'),(''),(':'),('::'),
('8:0900055'),(NULL);

DECLARE @Splitter VARCHAR(1) = ':';

SELECT 
    Code,
    CHARINDEX(@Splitter, Code) AS SplitPoint,
    CASE 
        WHEN CHARINDEX(@Splitter, Code) = 0 THEN 'NO splitter'
        WHEN CHARINDEX(@Splitter, Code) = 1 THEN 'NO LEFT'
        WHEN CHARINDEX(@Splitter, Code) > 1 THEN 
            LEFT(Code, CHARINDEX(@Splitter, Code) - 1)
        WHEN CHARINDEX(@Splitter, Code) IS NULL THEN 'OOPS'
        ELSE 'broken'
    END AS Split1
    ,
    CASE
        WHEN CHARINDEX(@Splitter, Code) = 0 THEN Code 
        WHEN CHARINDEX(@Splitter, Code) = 1 THEN RIGHT(Code,LEN(Code)- 1)
        WHEN CHARINDEX(@Splitter, Code) > 1 THEN 
          RIGHT(Code, LEN(Code )-1 -
                LEN(LEFT(Code, CHARINDEX(@Splitter, Code) - 1))
                )   
        WHEN CHARINDEX(@Splitter, Code) IS NULL THEN 'OOPS2'
        WHEN CHARINDEX(@Splitter, Code) > 1 THEN 'NO RIGHT'
        ELSE RIGHT(Code, LEN(Code)) + 'faile'
    END AS Split2
    ,
    CASE
        WHEN CHARINDEX(@Splitter, Code) = 0 THEN ' no splitter'
        WHEN CHARINDEX(@Splitter, Code) = 1 THEN ' =1'
        WHEN CHARINDEX(@Splitter, Code) > 1 THEN ' >1'
        WHEN CHARINDEX(@Splitter, Code) IS NULL THEN 'OOPS2'
        WHEN CHARINDEX(@Splitter, Code) > 1 THEN 'NO RIGHT'
        ELSE RIGHT(Code, LEN(Code)) + 'faile'
    END AS Split2By
FROM @Codes;

-- end code
    CREATE TABLE #MyTemp (Split1 VARCHAR(20),Split2 VARCHAR(20));

    INSERT INTO #MyTemp (Split1, Split2)
SELECT 
    CASE 
        WHEN CHARINDEX(@Splitter, Code) = 0 THEN ''
        WHEN CHARINDEX(@Splitter, Code) = 1 THEN ''
        WHEN CHARINDEX(@Splitter, Code) > 1 THEN LEFT(Code, CHARINDEX(@Splitter, Code) - 1)
        WHEN CHARINDEX(@Splitter, Code) IS NULL THEN Code
        ELSE Code -- 'broken'
    END AS Split1
    ,
    CASE
        WHEN CHARINDEX(@Splitter, Code) = 0 THEN Code 
        WHEN CHARINDEX(@Splitter, Code) = 1 THEN RIGHT(Code,LEN(Code)- 1)
        WHEN CHARINDEX(@Splitter, Code) > 1 THEN RIGHT(Code, LEN(Code )-1 - LEN(LEFT(Code, CHARINDEX(@Splitter, Code) - 1)))
        WHEN CHARINDEX(@Splitter, Code) IS NULL THEN Code
        WHEN CHARINDEX(@Splitter, Code) > 1 THEN ''
        ELSE Code -- 'fail'
    END AS Split2
FROM TableWithUnsplitCodes;

SELECT * FROM #MyTemp
-- WHERE --clause if you need to bypass any

Upvotes: 0

forpas
forpas

Reputation: 164174

All branches of a CASE statement must return the same data type.
When you return -1 at the else part, the value returned by this CASE is casted to integer so 09000 will turn to 9000.
You can change -1 to '-1'. 

DECLARE @CODE    VARCHAR(11) = '09000:09502';
DECLARE @Split1  VARCHAR(10) = '';
DECLARE @Split2  VARCHAR(10) = '';
SET @Split1      = CASE 
                     WHEN CHARINDEX(':',@CODE)>0 THEN LEFT(@CODE, CHARINDEX(':', @CODE)-1)
                     ELSE '-1' 
                   END
SET @Split2      = RIGHT(@CODE, LEN(@CODE)-CHARINDEX(':', @CODE));
PRINT(@Split1);
PRINT(@Split2);

This way you don't need the CAST() function.
Also you declared @CODE as VARCHAR(10) and this truncated the last char.
Change to VARCHAR(11).
Also I guess you want @Split2 to be assigned the right 5 chars of @CODE and not the left as in your code.

See the demo.

Upvotes: 2

Pratik Bhavsar
Pratik Bhavsar

Reputation: 858

As @Arulkumar mentioned, it is because your case statement is returning an integer. I simplified your code a bit: 

DECLARE @CODE    VARCHAR(10) = '09000:09502';
DECLARE @Split1  VARCHAR(10) = '';
DECLARE @Split2  VARCHAR(10) = '';

DECLARE @ColonIndex INT = CHARINDEX(':',@CODE)

SET @Split1      = CASE WHEN @ColonIndex > 0 THEN LEFT(@CODE, @ColonIndex-1)
                        ELSE '-1' END
SET @Split2      = LEFT(@CODE, 5);

PRINT(@Split1);
PRINT(@Split2);

Result:

enter image description here

Upvotes: 1

Arulkumar
Arulkumar

Reputation: 13237

In your CASE statement, the THEN and ELSE statement's result data type should be same. But in your query the ELSE block's return result is -1, so it considering the output of the case statement is INT, so it remove the leading zero from the result and return as integer, and you are expecting the output as varchar only.

So if you are modify your ELSE block to ELSE '' END or ELSE '-1' END it will return your expected result as varchar(10).

Upvotes: 1

Related Questions