CODEWITHSUNDEEP
sqlsql-serversplit
user2595971
user2595971

Reputation: 11

Splitting string and adding them to another table

I am using SQL server management studio express. I have a table with column names like: period, account, and so forth.

The account column contains values like 2abc-414-3136-12356478-000-0000-000

What I need to do is split the values and add them to another table, e.g. 2abc goes to company name, 414 goes to location code, 3136 to Department code, 12356478 to nat acc, 000 to pg, 0000 to ig, and 000 to Fut.

A detailed explanation would be deeply appreciated. Also I get confused as to where to write the code, please explain this too.

Upvotes: 1

Views: 121

Answers (1)

Joe Taras
Joe Taras

Reputation: 15399

I assume your information are the same (7 fields like your example)

declare @a varchar(500)

declare #crs insensitive cursor for
select account from mysourcetable
for read only
open #crs
fetch next from #crs into @a
while(@@FETCH_STATUS = 0)
begin

    declare @rest varchar(500)

    declare @field1 varchar(20)
    set @field1 = SUBSTRING(@a, 0, charindex('-', @a))
    set @rest = SUBSTRING(@a, charindex('-', @a) + 1, 100)

    declare @field2 varchar(20)
    set @field2 = SUBSTRING(@rest, 0, charindex('-', @rest))
    set @rest = SUBSTRING(@rest, charindex('-', @rest) + 1, 100)

    declare @field3 varchar(20)
    set @field3 = SUBSTRING(@rest, 0, charindex('-', @rest))
    set @rest = SUBSTRING(@rest, charindex('-', @rest) + 1, 100)

    declare @field4 varchar(20)
    set @field4 = SUBSTRING(@rest, 0, charindex('-', @rest))
    set @rest = SUBSTRING(@rest, charindex('-', @rest) + 1, 100)

    declare @field5 varchar(20)
    set @field5 = SUBSTRING(@rest, 0, charindex('-', @rest))
    set @rest = SUBSTRING(@rest, charindex('-', @rest) + 1, 100)

    declare @field6 varchar(20)
    set @field6 = SUBSTRING(@rest, 0, charindex('-', @rest))
    set @rest = SUBSTRING(@rest, charindex('-', @rest) + 1, 100)

    declare @field7 varchar(20)
    set @field7 = @rest

    insert into mytargettable (field1, field2, field3, field4, field5, field6, field7)
    values (@field1, @field2, @field3, @field4, @field5, @field6, @field7)

    fetch next from #crs into @a
end
close #crs
deallocate #crs

This result you can put in a loop with a cursor to scroll all over rows of your table result by your query. Instead of final select you can use an insert statement to populate your destination table.

Tell me if it's ok.

After edit: I've incapsulated the original split into the cursor. A cursor is a tool take the output of query and process every rows with fetch command. In your case, I put the field account value of your table in the @a variable.

@@FETCH_STATUS check if exists other rows to process. So at the end of iteration i put in the target table the result and go on.

This is a template, you must customize for your aim, if there are some sintax bug please fix because I've written that without a real test. Have a nice day

Upvotes: 1

Related Questions