Removing Leading Zeros 1

[gcoast]

What is the best way to replace leading zeros in a char field with blanks? Must keep the '2400' in positions 5-8.

'00002400' would become ' 2400'

Thanks,
gc

 

[SQLSister]

Are the records always four zeros and then four numbers you want to keep? Or does the number of numbers you want to keep vary?

And why do this anyway, why not just strip the leading zeros and right justifying the column in any reports or or application pages that show the number?

"NOTHING is more important in a database than integrity." ESquared

 

[ousoonerjoe]

Take a look at this post I had made a while back regarding a similar situation. It describes a Function for padding values.

Convert to a number then pad it out with a space.

thread183-1480960

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[gcoast]

The field is always eight characters long but may contain numbers or characters. The number of leading zeros will vary.
The database is being supplied by a 3rd party and we cannot modify the way they are storing the data.

Thanks,

gc

 

[gmmastros]

There is a trick you could use.

First, replace 0's with space.
LTrim the data (Removing spaces from the beginning)
Replace Space with 0.

Ex:

Declare @Data VarChar(8)

Set @Data = '00002400'
Select @Data -- '00002400'

Set @Data = Replace(@Data, '0', ' ')
Select @Data -- '    24  '

Set @Data = LTrim(@Data)
Select @Data -- '24  '

Set @Data = Replace(@Data, ' ', '0')
Select @Data -- '2400'

Put it all together....

Set @Data = Replace(LTrim(Replace(@Data, '0', ' ')), ' ', '0')

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gcoast]

Thanks for the replies. But as I said I must keep the '2400' in positions 5-8. The data will at times contain numbers and letters so I can't convert to an int.
Just need the final result to be ' 2400'.

Thanks,

gc

 

[ousoonerjoe]

Ok. then you can use a combination of the Pad Function and gmmastros' suggestion:

Set @Data = Padvalue(Replace(LTrim(Replace(@Data, '0', ' ')), ' ', '0'), 8, ' ')

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[gmmastros]

Fine. Add another step.

Declare @Data VarChar(8)

Set @Data = '00002400'
Select @Data -- '00002400'

Set @Data = Replace(@Data, '0', ' ')
Select @Data -- '    24  '

Set @Data = LTrim(@Data)
Select @Data -- '24  '

Set @Data = Replace(@Data, ' ', '0')
Select @Data -- '2400'

Set @Data = Right('        ' + @Data, 8)
Select @Data -- '    2400'

putting it all together...

Set @Data = Right('        ' + Replace(LTrim(Replace(@Data, '0', ' ')), ' ', '0'), 8)

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bborissov]

SELECT RIGHT(SPACE(LEN(YourField))+
            CAST(CAST(YourField as int) As varchar(8)),
            LEN(YourField))
FROM YourTable
WHERE ISNUMERIC(YourField+'e0')=1

NOT TESTED WELL

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[gcoast]

Thanks for all the replies,

Just what I needed.

gc

 

[gmmastros]

Another way (using obscure functions)

Declare @Data VarChar(8)

Set @Data = '00002400'

Select Stuff(@Data, 1, PatIndex('%[^0]%', @Data)-1, Space(PatIndex('%[^0]%', @Data)-1))

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gcoast]

George, I think I will use your last post as it keeps me from specifying the length of the field in the select statement.

Thanks again,

gc