How to move data from columnA to another columnB ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

How to move data from columnA to another columnB ?

Author

Topic

pamyral_279
Posting Yak Master

143 Posts

Posted - 2006-08-28 : 23:41:13

Table :
ColumnA ColumnB
ABCDEFGH
12345678
11223344X
55667788ZZ
7788221
223
6667
I want to move 4 character from third position in ColumnA to ColumnB

ColumnA ColumnB
ABCDEFGH-------CDEF
12345678-------3456
11223344X------2233
55667788ZZ-----6677
7788221--------8822
223------------3
6667-----------67

How can i do ?
Thank you very much !

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-28 : 23:50:03

[code]update table
set ColumnB = substring(ColumnA, 3, 4)[/code]

KH

pamyral_279
Posting Yak Master

143 Posts

Posted - 2006-08-29 : 00:10:13

quote:
Originally posted by khtan

update table
set ColumnB = substring(ColumnA, 3, 4)

KH

What happen if ColumnA is null or data is empty ?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-29 : 00:17:43

"What happen if ColumnA is null or data is empty ?"

Why don't you give it a try and see what is the result ?

KH

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-29 : 00:22:16

Here it goes..

declare @table table
(
	ColumnA 	varchar(100),
	ColumnB		varchar(4)
)
insert into @table (ColumnA)
select	'ABCDEFGH'	union all
select	'12345678'	union all
select	'11223344X'	union all
select	'55667788ZZ'	union all
select	'7788221'	union all
select	'223'		union all
select	'6667'		union all
select	''		union all
select	NULL

update 	@table
set 	ColumnB = substring(ColumnA, 3, 4)

select 	* 
from 	@table

/*
ColumnA        ColumnB 
-------------- ------- 
ABCDEFGH       CDEF
12345678       3456
11223344X      2233
55667788ZZ     6677
7788221        8822
223            3
6667           67
               
NULL           NULL

(9 row(s) affected)
*/

KH

pamyral_279
Posting Yak Master

143 Posts

Posted - 2006-08-29 : 00:27:23

quote:
Originally posted by khtan

"What happen if ColumnA is null or data is empty ?"

Why don't you give it a try and see what is the result ?

KH

Thank khtan very much !
Sorry for my question , because when i write code (not sql) i also use substring,it occur exception when data is empty.
Sorry for any inconvinent !

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-29 : 00:48:54

no problem

KH

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-29 : 01:29:21

And to close the MOVE operation, use

update table
set ColumnA = STUFF(ColumnA, 3, 4, '')

Otherwise, it is just a COPY operation.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources