Remove leading zeros from derived column


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

i have tried the following


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_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


please try this

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



please see this link also

hi thanks

ya found that one as well and tried


but it fails when it runs that part

this worked

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

more white space between the ""

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