Leading Zeros

[Former Member]

Hi,

Whats the simplest way to truncate leading zeros when updating a table using SQL Server 2005? Like the ltrim() in Oracle SQL Developer. Thanks.

Regards,

V M.

[khaleelurrehman_badeghar]

Hi,

You can use stringright( String , length ). It will help if length of string is fixed and number of zeroes is fixed.

If these are not fixed then you can use the combination of stringright( String , length ) and other string functions provided in the expression editor.

Regards

Khaleel

[Former Member]

Hello guy,

Input SQL Server in Stored Procedure

IF LEN(@ID_PRODU_ORDER) < 12

SET @ID_PRODU_ORDER = REPLICATE('0',(12 - LEN(@ID_PRODU_ORDER))) + @ID_PRODU_ORDER

Output SQL Server

CONVERT(int,A.ID_PRODU_ORDER)

Danilo

[Former Member]

REPLACE(LTRIM(REPLACE(id,'0',' ')),' ','0')

[agentry_src]

If you are passing the value to a query in a BLS, simply pass it in like this:

number(StringWithLeadZeroes).

The number function will remove all leading Zeroes and since the params are all strings anyways, it will work quickly. So 000000000010009833 becomes 10009833.

Mike

Edited by: Michael Appleby on Mar 19, 2009 8:01 PM

[Former Member]

Actually VM, Michael has the right idea in this case. Clean it up before calling you're query.

[Former Member]

Appreciate the ideas guys. I like the 'number(String)' function but it returns a '0' in case of alphanumeric values - like '00000ABCXYZ'. Not sure if to use it. Thanks.

Regards,

V M.

[Former Member]

Hi Venki,

For your case , you can do one thing.

-Get String length.

-loop on string and replace 0 with space until u get any alphabet or number,

-then break

u can use these functions or any SQL functions

stringtrim( stringreplace( "0000A" , 0, "") )

Try it

Regards

Padma.

[Former Member]

Thanks Padma. I could have data like this -

0000A00123456789

0000A00123456790

0000A00123456791

The 'stringreplace' would replace zeros anywhere on the string and the 'stringtrim' would remove white space from both sides of the string. Not sure if stringtrim( stringreplace( "0000A" , 0, "") ) would satisfy the scenario I have. Guess there isn't just one function like 'ltrim()' that would do the job. Thanks anyway.

Regards,

V M.

[Former Member]

hey Venki i hope u have not understood what solution i hv given.

See u take string length and repeat(loop) on that length, you need to replace leading zero one by one until u get numer or alphabet.

Then trim spaces of left.. U will get yor result.

hope u understood

Edited by: Rao on Mar 20, 2009 3:10 PM

[former_member381378]

In MII BLS, I think there is no direct way to do this. You need to do write some custom expression using the existing expression. Or the siplest one could be assigning the value to some integer variable in assignment action block, which trucates the leading zeros..

Thanks,

Soumen