• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 554
  • Last Modified:

SQL Query - Append ZERO's in front of column with keeping equal field characters

My dearest experts,

I have a table in which I have an Account Number ranging anywhere from 3 to 16 digits.  I'm using this table as part of a VIEW to extract data to push into another program.  I need to append "0"'s (zero's) to the front of all of the account numbers that are not 16 characters in length.

Example:   554433 needs to be 0000000000554433
Example:   87651234 needs to be 0000000087651234

Any and all assistance would be greatly appreciated.  

-Nick
0
NCollinsBBP
Asked:
NCollinsBBP
1 Solution
 
virtuadeptCommented:
SELECT REPLACE(STR(CONVERT(FLOAT,account_number),16,0),' ','0')

converts the account number to a float, then to a 16 precision string with leading spaces, and finally replaces the spaces with zeros.
0
 
David ToddSenior DBACommented:
Hi,

select right( '0000000000000000' + convert( varchar( 16 ), account_number), 16 )

Converts account_number to varchar( 16 ), prepends leading zeros, and takes the right-most 16 characters.

HTH
  David
0
 
NCollinsBBPAuthor Commented:
This did exactly what I needed it to do.  Thank you, virtuadept!
0
 
Chris LuttrellSenior Database ArchitectCommented:
I assume the results will have to be in character format to keep the leading zeros but this is a common way to to that with varying lengths for input.
Prepend the existing value with more than enough zeros and then take the RIGHT 16 of them for your result.

SELECT RIGHT(REPLICATE('0',16)+AN.Account_Number,16) Padded_AccountNumber
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now