HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

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

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
ignoreleadingzerosanythingdigitsthanlessandintegersadd

Problem

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.

Solution

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

Code Snippets

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

Context

StackExchange Database Administrators Q#155929, answer score: 6

Revisions (0)

No revisions yet.