Completing zeroes to a field

Question

Post reply

Completing zeroes to a field

chileu17

Hall of Fame

Points: 3093
More actions
January 2, 2009 at 9:09 am

#125315

Hi everyone, I need a little help with writing a query for completing a retrieved field with zeroes to the left. I have a field that is a varchar of 8 but I need to retrieve it like a varchar of 16 but the 8 first characters should be zeroes. Is there an easy way to do this? Like a convert or some function like that?
Thanks in advance 🙂

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 1

could you give us some examples please.

What would you expect to be returned from the following

12345678 = 0000000012345678?

123 = 00000000123 or 0000000000000123

0 = 000000000 or 0 or 0000000000000000

etc...

Thanks

Chris

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

null-512060 SSChasing Mays Points: 613 More actions · Answer 2

SELECT REPLICATE('0', 16 - LEN(LTRIM(RTRIM('SomeValue'))))

+ LTRIM(RTRIM('SomeValue'))

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Christopher Stobbs (1/2/2009)
could you give us some examples please.
What would you expect to be returned from the following
12345678 = 0000000012345678?
123 = 00000000123 or 0000000000000123
0 = 000000000 or 0 or 0000000000000000
etc...
Thanks
Chris

Or null.

For reference, here's generic left-padding:

DECLARE @MyColumn VARCHAR(8)

SET @MyColumn = '23'

SELECT CAST(RIGHT(REPLICATE('0', 16) + @MyColumn, 16) AS VARCHAR(16))

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 4

I guess it just depends how he wants his output?

Does he want a value of length 16 or is the max length 16?!?

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Christopher Stobbs (1/2/2009)
I guess it just depends how he wants his output?
Does he want a value of length 16 or is the max length 16?!?

Could be either! It's far from clear.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

chileu17 Hall of Fame Points: 3093 More actions · Answer 6

Thanks guys for the quick replies. What I wanted is a value of length 16 and the options you gave are great.

Thank you very much for your help.