Remove leading zeroes - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

Remove leading zeroes

Author

Topic

CoachBarker
Posting Yak Master

170 Posts

Posted - 2009-03-04 : 07:53:21

I have weights that are returned from the db with leading zeroes in the field (LoadPounds 001234, LoadPounds 012345 instead of LoadPounds 1234 or 12345). What is the correct way to remove the leading zeroes?

Thanks for the help
CoachBarker

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-04 : 08:39:40

[code]select
REPLACE(LTRIM(REPLACE('001234', ISNULL('0', '0'), ' ')),
' ', ISNULL('0', '0'))

select
convert(int,'001234')
[/code]

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-04 : 08:42:44

I don't know if you needed this done using reporting services. I just posted the sql which should do the same.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-04 : 09:14:10

isnt this enough?
SELECT LoadPounds*1
or

SELECT LoadPounds+0

CoachBarker
Posting Yak Master

170 Posts

Posted - 2009-03-04 : 09:31:08

I would like to be able to do this in the report not in the query.

Thanks for the help
CoachBarker

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-04 : 09:41:33

then just use

=Cint(Fields!Yourfield.value)

CoachBarker
Posting Yak Master

170 Posts

Posted - 2009-03-04 : 10:17:51

Returned this error message, I have 2 types of fields where I need to do this. One field is an int and the second field is a varchar(10).

for both fileds the error was

The Format expression used in textbox ‘textbox’ returned a data type that is not valid.

I put this expression in the format property for the textboxes, is that where I need to put it?

Thanks for the help
CoachBarker

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-04 : 11:29:27

nope. you need to put it in value expression

CoachBarker
Posting Yak Master

170 Posts

Posted - 2009-03-04 : 12:15:04

Thanks still trying to learn this on the fly.

Now in the report this works for all the fields but 1, for example without using CINT() the value is 010731, using the expression it is 110731731 or if it is 0900021 it shows up as 990002121. Any ideas, this field is an int coming into the report.

Thanks for the help
CoachBarker

CoachBarker
Posting Yak Master

170 Posts

Posted - 2009-03-04 : 12:43:44

I got it to work.

Thanks for the help
CoachBarker

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-04 : 13:00:37

quote:
Originally posted by CoachBarker

Thanks still trying to learn this on the fly.

Now in the report this works for all the fields but 1, for example without using CINT() the value is 010731, using the expression it is 110731731 or if it is 0900021 it shows up as 990002121. Any ideas, this field is an int coming into the report.

Thanks for the help
CoachBarker

are you applying any custom formats?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources