Update leading Zeros

Question

Post reply

Update leading Zeros

Arthur Lorenzini

SSC-Addicted

Points: 447
More actions
June 4, 2007 at 10:58 am

#179100

I have a varchar column that contains the some of the following data:
10036
10052
100633
10068
100855
100855
101053
101090
101136
101206
101383
101416
101419
101486
101486
101579
10169
10169
i need to a add leading zeros to the data that does not have a full 6 position already in it.
eg.:
010036
010052
100633
010068
100855
100855
101053
101090
101136
And I need to update the table with these new values. Any ideas woould be great.
Thanks,
Art

Viewing 14 posts - 1 through 13 (of 13 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Try this:

update dbo.mytable set

mycolumn = replicate('0', 6 - len(ltrim(rtrim(mycolumn)))) + mycolumn

where

len(ltrim(rtrim(mycolumn)) < 6

Arthur Lorenzini SSC-Addicted Points: 447 More actions · Answer 2

When I run the following code I get this error:

Msg 170, Level 15, State 1, Line 3

Line 3: Incorrect syntax near '<'.

update

dbo.RespondentProperties set

PropertyValue

= replicate('0', 6 - len(ltrim(rtrim(PropertyValue)))) + PropertyValue

where

len(ltrim(rtrim(PropertyValue)) < 6)

I also tried running as you submitted it and recieved the same error.

Thanks

Art

JacekO SSCertifiable Points: 6610 More actions · Answer 3

You are missing another ) before < 6

---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

JacekO, thanks for the catch on the missing paren.

Jeff Moden SSC Guru Points: 1004250 More actions · Answer 5

Just another way...

SELECT REPLACE(STR(somecol,6),' ','0')

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

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

Found this on our dev server:

-- Author:  Igor Nikiforov,  Montreal,  EMail: udfs@sympatico.ca   
 -- PADL(), PADR(), PADC() User-Defined Functions
 -- Returns a string from an expression, padded with spaces or characters to a specified length on the left or right sides, or both.
 -- PADL similar to the Oracle function PL/SQL  LPAD 
CREATE function PADL  (@cString nvarchar(4000), @nLen smallint, @cPadCharacter nvarchar(4000) = ' ' )
returns nvarchar(4000)
as
  begin
        declare @length smallint, @lengthPadCharacter smallint
        if @cPadCharacter is NULL or  datalength(@cPadCharacter) = 0
           set @cPadCharacter = space(1) 
        select  @length = datalength(@cString)/(case SQL_VARIANT_PROPERTY(@cString,'BaseType') when 'nvarchar' then 2  else 1 end) -- for unicode
        select  @lengthPadCharacter = datalength(@cPadCharacter)/(case SQL_VARIANT_PROPERTY(@cPadCharacter,'BaseType') when 'nvarchar' then 2  else 1 end) -- for unicode

        if @length >= @nLen
           set  @cString = left(@cString, @nLen)
        else
        begin
              declare @nLeftLen smallint
              set @nLeftLen = @nLen - @length  -- Quantity of characters, added at the left
              set @cString = left(replicate(@cPadCharacter, ceiling(@nLeftLen/@lengthPadCharacter) + 2), @nLeftLen)+ @cString
           end

    return (@cString)
  end

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

Arthur Lorenzini SSC-Addicted Points: 447 More actions · Answer 7

Thanks for everybodies input. I went with Lynn original code and it worked great. Thanks you all.

Tom Goltl SSCertifiable Points: 5815 More actions · Answer 8

Even though your done, here's on that I use.

declare @vc as varchar(6)

set @vc = '123'

select RIGHT('000000',6-LEN(@vc))+@vc

Tom

Joel Ewald SSCertifiable Points: 6120 More actions · Answer 9

or

declare

@vc as varchar(6)

set

@vc = '123'

select

RIGHT('000000' + @vc,6)

B.Saminathan-224270 SSC Veteran Points: 281 More actions · Answer 10

try this

use northwind

select substring(cast((1000+productid)as varchar(4)),2,3) from products

Tom Goltl SSCertifiable Points: 5815 More actions · Answer 11

Tom Goltl

SSCertifiable

Points: 5815

June 6, 2007 at 7:55 am

#711121

Nice Joe.

CherokeeWoman Valued Member Points: 68 More actions · Answer 12

I just joined this site last week and I love it. 😀

This solution:

SELECT REPLACE(STR(somecol,6),' ','0')

by Jeff Moden

is just what I was looking for to (simply) export a column (adding leading zeros) from a DTS package to a text file.

Thanks Jeff (and all)

Jeff Moden SSC Guru Points: 1004250 More actions · Answer 13

You bet. Welcome aboard and thank you for the feedback. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)