Remove Leading Zeros in a column in a sql server table,

Question

My question is very similar to this one:

SE Question

However I want to run a query on a table, and actually update the values in that table, not just Select them.

Im not sure how to select a value and then Update it without using a for loop.

What Query can I run to do this?

{Edit:}

I store number values as a varchar and an import I ran imported numbers such a 00233 I want it to be 233

Answer 1

So adapt the query for an update:

update table t
    set ColumnName =  substring(ColumnName, patindex('%[^0]%', ColumnName), 10);

Answer 2

You question is a little vague, since you don't elaborate on WHY you want to SELECT and then UPDATE, because of that this answer may be of no use to you. However..

In an UPDATE statement you can set a column value based on a column value. What I mean is, if you have "00001A" in ColA and want it to be "1A" you can (mostly taking answer from linked question)

UPDATE MyTable SET ColA =  substring(ColA, patindex('%[^0]%',ColA), 10)

First time through this code will change any occurance of 00001A to 1A and subsequent runs it won't match the PATINDEX so ignore the value.

If this isn't what you're after, please expand upon the question a little more and hopefully you'll get more assistance.

Answer 3

If all the columns are numeric use this:

UPDATE table
SET col =  cast(cast(col as int) as varchar(10))
WHERE col like '0%'

If some values are not numeric

UPDATE table
SET col = stuff(@a, 1, patindex('%[^0]%', @a) - 1, '')
WHERE col like '0%'