IF then ?

patsq

How do I set this in a Sql query? If code >= 1 or <= 9 then place two zeros if front...001, 002....009 if code >= 10 or <= 99 then place one zero if front....010, 050...,099 Thanks **** NEVER MIND, I GOT IT:*********

when [EMPLOYEE].JOB_CODE >=1 AND [EMPLOYEE].JOB_CODE <= 9 then '00' + [EMPLOYEE].JOB_CODE
when [EMPLOYEE].JOB_CODE >=10 AND [EMPLOYEE].JOB_CODE <= 99 then '0' + [EMPLOYEE].JOB_CODE

PIEBALDconsult

CASE

If current SQL Server, look at FORMAT.

Lost User

Don't you mean ...

If code >= 1 AND <= 9 then place two zeros if front...001, 002....009
if code >= 10 AND <= 99 then place one zero if front....010, 050...,099

... ?

Richard Deeming

For Microsoft SQL 2012 or higher, use FORMAT[^]:

FORMAT(code, 'D3')

For older versions, use CASE[^]:

CASE
WHEN code Between 1 And 9 THEN '00'
WHEN code Between 10 And 99 THEN '0'
ELSE ''
END + CAST(code As varchar(10))

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

patsq

Correct - was in a rush to leave the office when I typed it. I want to concatenate 00 in front of the value.

Chris Quinn

you can do it without any case statement like this:

SELECT Right('00' + Convert(Varchar(10),[code]),2) as [Code]

========================================================= I'm an optoholic - my glass is always half full of vodka. =========================================================

Richard Deeming

Firstly, the second argument to Right should be 3, not 2. And secondly, if the input number is 1000 or higher, that will strip the leading digits:

WITH cte (code) As
(
SELECT 1
UNION ALL SELECT 10
UNION ALL SELECT 100
UNION ALL SELECT 1000
UNION ALL SELECT 10000
)
SELECT
code,
RIGHT('00' + CONVERT(varchar(10), code), 3)
FROM
cte
;

Output:

1 001
10 010
100 100
1000 000
10000 000

It's not clear whether that's what the OP wants, but I doubt it. :)

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

Chris Quinn

You are correct to point out my mistake, but it's easily fixed. There is, of course another way:

SELECT Format([Code],'000') as [Code]

========================================================= I'm an optoholic - my glass is always half full of vodka. =========================================================

Richard Deeming

Chris Quinn wrote:

There is, of course another way:

Which was the first thing I suggested above[^]! :laugh:

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

Pradeep Magati

Hello, Use REPLICATE() and RIGHT Functions,

SELECT RIGHT(REPLICATE(0,2)+CAST([EMPLOYEE].JOB_CODE AS NVARCHAR(200)),3) FROM YourTable

Regards, Pradeep M