UPDATE multiple values in table t-sql

Question

I have multiple columns where one of them has varchar values as follow.

I want to find any record that has values starting with 0 > remove that 0 (so 02 > 2) and update that field so there is no value starting with 0.

How can I go about doing that instead of manually updating each record that has 0 in the front? Is that even possible?

Thank you

Why is this column varchar at all? Why not simply converting the whole column to integer? — fancyPants
– fancyPants, Commented Feb 28, 2013 at 16:10
I am not really sure behind the reason for choosing varchar data type. converting the whole column to int sounds like a good idea. — 007
– 007, Commented Feb 28, 2013 at 16:26

Gordon Linoff · Accepted Answer · 2013-02-28 16:09:42Z

5

The following code removes the leading zeros by casting the value to an integer and back to a character string:

update t
    set col = cast(cast(col as int) as varchar(255))
    where t.col like '0%'

answered Feb 28, 2013 at 16:09

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

This column is right now varchar > I want to remove all the leading 0's and convert it to int. would disregarding outer case in your query do it?

Taryn · Accepted Answer · 2013-02-28 16:12:49Z

2

You can use the following:

update yourtable
set col = substring(col, 2, len(col)-1)
where left(col, 1) = '0'

answered Feb 28, 2013 at 16:12

Taryn

249k57 gold badges374 silver badges409 bronze badges

Korijn · Accepted Answer · 2013-02-28 16:11:09Z

1

Run this query until everything is filtered out I guess...

UPDATE Table SET Column=RIGHT(Column, LEN(Column)-1) WHERE Column LIKE '0%';

[Edit] Gordon's approach is probably better.

answered Feb 28, 2013 at 16:11

Korijn

1,40312 silver badges27 bronze badges