patternsqlMinor
Add leading zeros to integers less than 7 digits and ignore anything 7+
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.
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
Select RIGHT(rtrim('0000000' + cast(column as nvarchar)), 7) from tableI 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 tableCode 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 tableContext
StackExchange Database Administrators Q#155929, answer score: 6
Revisions (0)
No revisions yet.