update a column? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

update a column?

Author

Topic

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2007-04-16 : 09:57:49

Hi,

i have a column which has values like,

01234
00445
00067
....
i want to update those column like,

1234
445
67

how can i do it?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-16 : 10:06:24

[code]-- sample data
declare @t table
(
a varchar(10)
)

insert @t
select '01234' union all
select '00445' union all
select '00067'

-- final query
update @t
set a = convert(varchar(10), cast(a as int))

select * from @t[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-16 : 10:09:15

Another solution:

-- sample data
declare @t table
(
	a varchar(10)
)

insert @t 
select '01234' union all
select '00445' union all
select '00067' union all
select '002707' union all
select '0460'

-- final query
update @t
set a = replace(ltrim(replace(a, '0', ' ')), ' ', '0')

select * from @t

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-04-16 : 10:15:39

You shouldnt have used varchar to store numbers
If you want to show formatted data in front end, use format function there

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources