Link to home
Start Free TrialLog in
Avatar of printmedia
printmedia

asked on

Remove leading zeros in SQL task

Hi all.

We have a table (myTable) that has a field (PO_Number) that may have leading zeros.

For example:

PO_Number:
00123
04560
000ABC

I want to know if there is a way to remove any leading zeros, the number of leading zeros may vary, some may not have any leading zeros.

I will use this in a SQL DTS.

Thank you in advance!
ASKER CERTIFIED SOLUTION
Avatar of bchoor
bchoor
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Lee
Lee
Flag of United Kingdom of Great Britain and Northern Ireland image
Try this.

Lee
declare @x nvarchar(100)

--select @x = '87980123'
--select @x = '0087980123'
--select @x = '000087980123'
--select @x = '000087980123'
--select @x = 'asdasd87980123'
--select @x = '000asdasda87980123'
--select @x = 'adsa000adsd87980123'
select @x = 'sdadsa87980123'


while left(@x, 1) = '0'
begin
    select @x = right(@x, len(@x) - 1)
end

select @x

Open in new window

Avatar of Lee
Lee
Flag of United Kingdom of Great Britain and Northern Ireland image
Nice trick BC. Like it.

Lee
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image
>>I will use this in a SQL DTS.<<
Using T-SQL or using an ActiveX Script Task?
Avatar of printmedia
printmedia

ASKER

Thanks BC, I will give it a try and let you know.

ACperkins: I'll be using T-SQL
Avatar of printmedia
printmedia

ASKER

Worked great. Thanks!