This is how I do it:
UPDATE <tablename> SET <column> = SUBSTRING(<column>, PATINDEX('%[^0]%', <column>), CHAR_LENGTH(<column>)) WHERE <column> LIKE '0%'
Obviously, the most interesting part for me is the:
'%[^0]%'
construct that finds the position of the first non-ZERO character in the <column> to start substring from.
For detailed info about used functions, please visit Sybase’s documentation:
– patindex
How can I do the same but for a VARCHAR ?
Hi Ricky,
You can use the same query. Give it a try. If it does not work, paste here your test data and I’ll try to help.
Regards.