3

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.

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

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
| improve this answer | |
3

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.
| improve this answer | |
  • 1
    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
3

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
| improve this answer | |
2

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.

| improve this answer | |
  • 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
  • 2
    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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.