sql statement
-
Hi,
I have a Table1 like as follows
Col1 col2 col3 aaa bbb cc/dd/ee/ff/gg aaa ccc hh/rr/tt/ss/yy I need to store Table1 data into Table2 as follows
colA ColB ColC ColD ColE ColF ColG aaa bbb cc dd ee ff gg aaa ccc hh rr tt ss yy Is there any easy way to do this? Thanks.
-
I'm sure I have oversimplified this but given the sample data, this should get you started...
insert into Table2
select Col1, Col2, substring(Col3, 1, 2), substring(Col3, 4, 2), substring(Col3, 7, 2), substring(Col3, 10, 2), substring(Col3, 13, 2) from Table1
**ASCII stupid question, get a stupid ANSI !!!** -
Thanks for your response, but cc/dd/ee/ff/gg may not in standard length in other words it can be ccc/dddddddd/eeeeeeeeeeeeee/fffffffff/gg, so substring doesn't work.
-
will there be only 4 "/" or can be more?
-
Try this ...
-- clean up : DO NOT RUN ON PRODUCTION DB : tables will be dropped !!
if db_name() = 'tempdb'
begin
declare @object_id int
set @object_id = object_id('dbo.fn_colsplit')
if @object_id is not null drop function dbo.fn_colsplit
set @object_id = object_id('dbo.Table1')
exec sp_MSdrop_object @object_id
set @object_id = object_id('dbo.Table2')
exec sp_MSdrop_object @object_id
end
go
-- create a function that splits the col3 into 5 columns
create function dbo.fn_colsplit
(
@col3 varchar(255)
)
returns @result table ( ColD varchar(255),ColE varchar(255),ColF varchar(255),ColG varchar(255),ColH varchar(255) )
as
begin
declare @ColD varchar(255),@ColE varchar(255),@ColF varchar(255),@ColG varchar(255),@ColH varchar(255)
select @ColD = left(@col3,charindex('/',@col3)-1),
@ColE = substring(@col3+'/',len(@ColD) +2,charindex('/',@col3+'/',len(@ColD) +2)-len(@ColD) -2),
@ColF = substring(@col3+'/',len(@ColD)+len(@ColE) +3,charindex('/',@col3+'/',len(@ColD)+len(@ColE) +3)-len(@ColD)-len(@ColE) -3),
@ColG = substring(@col3+'/',len(@ColD)+len(@ColE)+len(@ColF) +4,charindex('/',@col3+'/',len(@ColD)+len(@ColE)+len(@ColF) +4)-len(@ColD)-len(@ColE)-len(@ColF) -4),
@ColH = substring(@col3+'/',len(@ColD)+len(@ColE)+len(@ColF)+len(@ColG)+5,charindex('/',@col3+'/',len(@ColD)+len(@ColE)+len(@ColF)+len(@ColG)+5)-len(@ColD)-len(@ColE)-len(@ColF)-len(@ColG)-5)
insert @result values ( @ColD , @cole , @ColF , @ColG , @ColH )
return
end
go
-- test Table 1
create table dbo.Table1
(
Col1 varchar(255),
col2 varchar(255),
col3 varchar(255)
)
go
insert Table1 values ( 'aaa','bbb','cc/dd/ee/ff/gg' )
insert Table1 values ( 'aaa','ccc','hh/rr/tt/ss/yy' )
insert Table1 values ( 'aaa','ddd','1/22/333/4444/55555' )
go
-- test Result Table 2
create table Table2
(
colA varchar(255),
ColB varchar(255),
ColC varchar(255),
ColD varchar(255),
ColE varchar(255),
ColF varchar(255),
ColG varchar(255)
)
go
-- transform Table1 to Table2
declare @Col1 varchar(255),@col2 varchar(255),@col3 varchar(255)
declare tst cursor local for select * from Table1
open tst
goto next_tst
while @@fetch_status = 0
begin
insert Table2
select colA = @Col1,
ColB = @col2,
ColC = F.ColD,
ColD = F.ColE,
ColD = F.ColF,
ColF = F.ColG,
ColG = F.ColH
from dbo.fn_colsplit(@col3) F
next_tst: fetch tst into @Col1,@col2,@col3
end
close tst
deallocate tst
go
-- show Rsults
select * from Table2
go
-
SUBSTRING will work, but it will be more complicated, and will need CHARINDEX to calculate length. For the first 2 columns (C and D) it could be like that:
CREATE TABLE #tbl1(col1 varchar(20), col2 varchar(20), col3 varchar(100))
CREATE TABLE #tbl2(colA varchar(20), colB varchar(20), colC varchar(20),
colD varchar(20), colE varchar(20),colF varchar(20), colG varchar(20))
INSERT INTO #tbl1
SELECT 'aaa','bbb','cc/dd/ee/ff/gg'
UNION
SELECT 'aaa','ccc','ccc/dddddddd/eeeeeeeeeeeeee/fffffffff/gg'
INSERT INTO #tbl2 (colA,colB,colC,colD)
SELECT col1, col2, col3,
SUBSTRING( col3,
1,
CHARINDEX('/',col3)-1),
SUBSTRING( col3,
CHARINDEX('/',col3)+1,
CHARINDEX('/', SUBSTRING(col3,CHARINDEX('/',col3)+1,LEN(col3)))-1)
FROM #tbl1
Then it begins to be too complicated, so it would probably be better to write a parsing UDF and call it recursively (unfortunately I can't go on - got some work that has to be done right now - but I'm sure someone will be able to give you advice).
-
Here's one method (which doesn't use a UDF) - just run it to see...
--data
declare @t1 table (Col1 varchar(20), col2 varchar(20), col3 varchar(200))
insert @t1
select 'aaa', 'bbb', 'cc/dd/ee/ff/gg'
union all select 'aaa', 'ccc', 'hh/rr/tt/ss/yy'
union all select 'aaa', 'ddd', 'ccc/dddddddd/eeeeeeeeeeeeee/fffffffff/gg'
declare @t2 table (colA varchar(20), ColB varchar(20), ColC varchar(20), ColD varchar(20), ColE varchar(20), ColF varchar(20), ColG varchar(20))
--calculation
insert into @t2 (colA, colB) select Col1, col2 from @t1
declare @i1 int
declare @i2 int
declare @i3 int
declare @i4 int
update t2
set @i1 = charindex('/', t1.col3),
colc = left(t1.col3, @i1-1),
@i2 = charindex('/', t1.col3, @i1+1),
cold = substring(t1.col3, @i1+1, @i2-@i1-1),
@i3 = charindex('/', t1.col3, @i2+1),
cole = substring(t1.col3, @i2+1, @i3-@i2-1),
@i4 = charindex('/', t1.col3, @i3+1),
colf = substring(t1.col3, @i3+1, @i4-@i3-1),
colg = substring(t1.col3, @i4+1, 200)
from @t2 t2
inner join @t1 t1 on t2.ColA = t1.Col1 and t2.ColB = t1.Col2
--results
select * from @t2
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
Looks nice, Ryan... that's about what I hoped to get together, but it is a lot easier than I thought it would be when written in a direct statement (without UDF). I didn't realize that I can use variables this way in an update statement.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply