0

I want to take a string like this 7;132030;00102 from column misc2 in table data_import and insert 7 into column Department and insert 132030 into column job and 00102 in column CostCode in table Data_Timecards.

Table Data_Import

enter image description here

What Table Data_Timecards should look like after insert

enter image description here

BrianMichaels
  • 522
  • 1
  • 7
  • 16
  • Sorry, there is no function like split() in mssql. You need to do this with the transact-sql string manipulation functions available. Maybe somebody will give you an example code here. – peterh Dec 08 '13 at 19:22
  • This is probably the closest to what the OP is asking for: http://stackoverflow.com/questions/9044011/sql-server-split-by-comma essentially by using a combination of `substring` & `charindex` you can split `misc2` string which is delimited by comma and split it into three columns. Regarding how to insert it into another table (assuming you have a common column between the 2 tables) you could use `update` with an `inner/left join` to acheive it. –  Dec 08 '13 at 19:49

1 Answers1

1

Pretty ugly looking solution but will do the job

Query

DECLARE @Var NVARCHAR(100) = '7;132030;001202'

SELECT  LEFT(@Var, CHARINDEX(';', @Var) - 1)     AS [Department]
        ,SUBSTRING(@Var, CHARINDEX(';', @Var) + 1, 
         LEN(@Var)- LEN(LEFT(@Var, CHARINDEX(';', @Var)))- 
         LEN(RIGHT(@Var, CHARINDEX(';', REVERSE(@Var))))) AS [Job]
       , RIGHT(@Var, CHARINDEX(';', REVERSE(@Var))-1) AS [CostCode]

Result Set

Department  Job     CostCode
    7       132030  001202

Data Into Destination Table

INSERT INTO Destination_Table_Name (Department, Job, CostCode)

SELECT  LEFT(misc2, CHARINDEX(';', misc2) - 1)   
        ,SUBSTRING(misc2, CHARINDEX(';', misc2) + 1, 
         LEN(misc2)- LEN(LEFT(misc2, CHARINDEX(';', misc2)))- 
         LEN(RIGHT(misc2, CHARINDEX(';', REVERSE(misc2))))) 
       , RIGHT(misc2, CHARINDEX(';', REVERSE(misc2))-1) 
 FROM Source_Table
M.Ali
  • 67,945
  • 13
  • 101
  • 127