Sql-server – Last character in column shift to next column

sql serverssist-sql

I have a unique situation I have to fix. Our vendor gave us a data map and things are not mapping out as they said it should. My situation is, starting with a particular column, I need to drop the last character from the column and append it to the next column over. I am at a loss for how to accomplish this.

I would adjust my ssis project and reload the data, however some of the data files were deleted and the infrastructure guys were not snapshotting that folder so I would be missing approx 10 days work of data in the warehouse.

edit

    create table dda
(
    comp_acct_num varchar(17)
    ,internet_code char(3)
    ,type_code char(1)
    ,cr_rate_code char(1)
)

INSERT INTO dda (comp_acct_num,internet_code,type_code,cr_rate_code) 
VALUES ('00000000000000000','111','7',''),('00000365874001000','111','7',''), 
       ('00000000000001000','211','7',''),('00003101118001000','211','7','')

The values inserted are the values as they exist now. They need to read like this:

comp_acct_num    |internet_code|type_code|cr_rate_code|
-------------------------------------------------------------
00000000000000000|  111        |    7    |            |
00000365874001000|  111        |    7    |            |
00000000000001000|  211        |    7    |            |
00003101118001000|  211        |    7    |            |

It should read like this:

comp_acct_num    |internet_code|type_code|cr_rate_code|
-------------------------------------------------------------
0000000000000000 |  011        |    1    |  7         |
0000036587400100 |  011        |    1    |  7         |
0000000000000100 |  021        |    1    |  7         |
0000310111800100 |  021        |    1    |  7         |

Best Answer

Here is a trivial way using left, right, and stuff.

declare @table table (column1 varchar(16), column2 varchar(16))

insert into @table
values
('abcdef','ghijkl')

update @table
set 
    column1 = left(column1,len(column1) - 1)
    ,column2 = stuff(column2,1,0,right(column1,1))

select * from @table

If you have trailing spaces, you'll need to use RTRIM accordingly.

column2 = stuff(column2,1,0,right(rtrim(column1),1))

After fixing your sample data to add commas in your DDL, and removing the extra column from the insert, you can still use the method above with a small tweak:

declare @dda table
(
    comp_acct_num varchar(17)
    ,internet_code char(3)
    ,type_code char(1)
    ,cr_rate_code char(1)
)

INSERT INTO @dda (  comp_acct_num,
                    internet_code,
                    type_code,
                    cr_rate_code) 
VALUES 
 ('00000000000000000','111','7','')
,('00000365874001000','111','7','')
,('00000000000001000','211','7','')
,('00003101118001000','211','7','')

update @dda
set 
    comp_acct_num = left(comp_acct_num,len(comp_acct_num) - 1)
    ,internet_code = left(stuff(internet_code,1,0,right(comp_acct_num,1)),3)

select * from @dda
Related Question