# Add leading zeros to integers less than 7 digits and ignore anything 7+

I have a query and it almost doing what I want. It is adding leading zeros but when it comes to things 7+ long it turns everything into a 7 number digit.

``````Select RIGHT(rtrim('0000000' + cast(column as nvarchar)), 7) from table
``````

I just think my brain is not working and I cannot figure this out. Can anyone help me with this?

Examples:

```324 -> 0000324
2232 -> 0002232
3324124 -> 3324124
```

All I'm trying do is add 0s to anything that is less than 7.

The column is `nvarchar`.

• If the column is nvarchar, why the `cast(column as nvarchar)`? – ypercubeᵀᴹ Nov 22 '16 at 0:59

Ugly (and won't perform all that great), but

``````SELECT
CASE WHEN len(Column) > 7 THEN CAST(Column AS nvarchar(20))
ELSE RIGHT(rtrim('0000000' + CAST(column as nvarchar(20)), 7) END
FROM table
``````

This is the most elegant solution I can think of:

``````Select isnull(replicate('0', 7 - len(column)),'') + rtrim(column) from table
``````

Examples:

``````Select isnull(replicate('0', 7 - len('123')),'') + rtrim('123')
Output: 0000123

Select isnull(replicate('0', 7 - len('1234567')),'') + rtrim('1234567')
Output: 1234567

Select isnull(replicate('0', 7 - len('12345678')),'') + rtrim('12345678')
Output: 12345678
``````

Explanation:

• The cast isn't required because your column is already a varchar
• the rtrim might as well be around the shortest value possible.
• replicate() returns NULL if the value is negative, so we replace this with an empty string.
• I was looking for a way of solving this using REPLICATE too but didn't realise that specifying a negative length would yield a null, so I tried to get rid of a negative value somehow and came up with this: `(7 - LEN(@n)) * ~CAST(LEN(@n) / 7 AS bit)`. That evaluates to 0 if the length is 7 or greater. No need for using ISNULL, therefore, but the resulting code is too obfuscated. The ISNULL way is much superior (IMO), cheers. – Andriy M Dec 2 '16 at 21:38

You can also convert it to integer and then back to string using a format specifier

``````SELECT FORMAT(TRY_CONVERT(INT, YourCol), 'D7')
FROM YourTable
``````

If you are absolutely sure that the maximum length of nvarchar 'column' will never exceed 13 characters then you can also try the below solution. It's an interesting solution but not the best one.

``````SELECT
RIGHT(
RTRIM('0000000' + CAST(column as nvarchar))
, (LEN(column) % 7)*(LEN(column) / 7) + 7
)
FROM table
``````

More description for the logic behind `(LEN(column) % 7)*(LEN(column) / 7) + 7`

Query logic provided in the question itself does the following: Take an example of string 'ABCD'.

1. Append 7 zeros to left hand side of the string. ( 0000000ABCD )
2. Take the rightmost 7 characters ( 0000 - 000ABCD )

As per the question, consider the column length is `n` and `x` is the desired length of the column based on the logic.

``````LEN(Column) = n
If n < 7 THEN Append (7-n) zeros to left, x = 7
If n >= 7 THEN Sting remains same, x = n
``````

Logic defined in the question does not works for string beyond length 7 because it statically pulls 7 rightmost characters. It should pull `x` number of characters based on the above condition. So we just need to come up with a logic which will generate `x` based on the length of sting/column.

So the final query should be `RIGHT(RTRIM('0000000' + CAST(column as nvarchar)),x)`

Logic says if `LEN(Column) < 7 then x should be 7` hence `LEN(Column) / 7 + 7` This will provide the result as 7 for n = {1 to 6}. Once `LEN(Column)` exceeds 6 then it will start providing result as 8. Hence multiply it with the reminder to 7.

`(LEN(column) % 7)*(LEN(column) / 7) + 7`

So till 1 to 6, `LEN(Column)/7` will be 0 hence the result would be 7. When its 7, `LEN(Column)%7` will be 0 hence the result would be 7. When length increases beyond 7, then `LEN(Column) % 7` will provide number of characters to be added after 7, and `LEN(Column) / 7` will be 1. Hence after 7 result will increase directly and linearly proportional with the length of string.

• OK. It works only for LEN <= 13 as you already noted. Why not the `CASE WHEN LEN(column) <= 7 THEN 7 ELSE LEN(column) END` which works for arbitrary lengths? – ypercubeᵀᴹ Dec 1 '16 at 13:16
• Agreed. `CASE WHEN LEN(column) <= 7 THEN 7 ELSE LEN(column) END` is the best solution. But its already pointed out by @Nic in the answer. No glory in reusing the same logic in `RIGHT()` function. But wanted to document the interesting way to handle the conditional statements using the mathematical operations. Hence I have already mentioned that this is not the best possible solution but an interesting one for the brainstorming. :) – SwapnilBhate Dec 1 '16 at 13:21