Remove leading zeros from derived column

SSIS and Import/Export
1

hi

i want to move leading zeros from a column. its a unicode column.

i have tried the following

REPLACE(LTRIM(REPLACE(Security,"0","")),"","0")
(DT_STR,30,1252)REPLACE(LTRIM(REPLACE(Security,"0","")),"","0")

They work but remove all zeros. I just want to remove any zeros at the start of the string and thats it. if any.

also tried this
(DT_STR,30,1252)(DT_I4)Security
(DT_WSTR, 20)(DT_I4)Security

they just fail

so here is what im looking for in output

04635RUP8 -4635RUP8
001306AB5 - 1306AB5
00135TAB0 - 135TAB0

what im getting from the logic that works

04635RUP8 -4635RUP8
001306AB5 - 136AB5
00135TAB0 - 135TAB

any help would be great

2

hi

please try this

SELECT
SUBSTRING (Col1, PATINDEX('%[^0 ]%', Col1 + ' '), LEN(Col1))
FROM Table1

GO

3

hi

please see this link also

4

hi thanks

ya found that one as well and tried

(DT_WSTR,50)(DT_I8)[YourInputColumn]

but it fails when it runs that part

5

this worked

REPLACE(LTRIM(REPLACE(Security,"0"," "))," ","0")

more white space between the ""

6

You should test that fo performance against other methods. :wink: