Integer PadLeft function in T-SQL

by

I believe this may be what your looking for:

SELECT padded_id = REPLACE(STR(id, 4), SPACE(1), '0') 

FROM tableA

or

SELECT REPLACE(STR(id, 4), SPACE(1), '0') AS [padded_id]

FROM tableA

I haven’t tested the syntax on the 2nd example. I’m not sure if that works 100% – it may require some tweaking – but it conveys the general idea of how to obtain your desired output.

EDIT

To address concerns listed in the comments…

@pkr298 – Yes STR does only work on numbers… The OP’s field is an ID… hence number only.

@Desolator – Of course that won’t work… the First parameter is 6 characters long. You can do something like:

SELECT REPLACE(STR(id,
(SELECT LEN(MAX(id)) + 4 FROM tableA)), SPACE(1), '0') AS [padded_id] FROM tableA

this should theoretically move the goal posts… as the number gets bigger it should ALWAYS work…. regardless if its 1 or 123456789…

So if your max value is 123456… you would see 0000123456 and if your min value is 1 you would see 0000000001

More Related Contents:

  1. T-SQL Split Word into characters
  2. How to compare strings in golang? [closed]
  3. Convert integers to strings to create output filenames at run time
  4. What is the difference between \r\n, \r, and \n? [duplicate]
  5. Ukkonen’s suffix tree algorithm in plain English
  6. Base64 length calculation?
  7. Getting and removing the first character of a string
  8. Convert String to Float in Swift
  9. Find index of last occurrence of a sub-string using T-SQL
  10. Generate random alphanumeric string in Swift
  11. How to search for a string in cell array in MATLAB?
  12. Convert Vec into a slice of &str in Rust?
  13. How does the lifetime work on constant strings / string literals?
  14. Turn float into string
  15. How to capitalize the first letter of a string in dart?
  16. Escape double quote in VB string
  17. Scala String vs java.lang.String – type inference
  18. SQL to find first non-numeric character in a string
  19. Swift, Strings and Memory Addresses
  20. Is there a way to create a String from utf16 array in swift?
  21. How can I use a dynamic format string with the format! macro?
  22. How to create a formatted String out of a literal in Rust?
  23. Remove last character from string. Swift language
  24. Replace String in all files in Eclipse [closed]
  25. Extract the first (or last) n characters of a string
  26. Insert line breaks in long string — word wrap
  27. Build a Comma Delimited String
  28. Swift – Split string over multiple lines
  29. Groovy String to Date
  30. What is the difference between VBScript’s + and & operator?

Leave a Comment