CODEWITHSUNDEEP
sql-server
gillers322
gillers322

Reputation: 249

Splitting Data Into Separate Columns Formula

We are using an external quiz tool for students which they access from a link in our LMS. When they click the link we are able to pass parameters for StudentID, CourseID, ModuleID etc. The quiz tool lets us record this variable in a single column so it could look like 034-23-12 or Student034-Course23-Module12 etc

Using Zapier, we can get the results added to our MSSQL database and then results can then appear on the student account.

However, to make this work we need to be able to split single column record into 3 columns - StudentID, CourseID and ModuleID 

QuizRecord    StudentID  CourseID ModuleID
034-23-12        34          23      12

Any help with this formula will be much appreciated.

Upvotes: 1

Views: 49

Answers (2)

user1529235
user1529235

Reputation:

You could use 'parsename' as you have 4 (or less) splits

Declare @data varchar(20)

Set @data='034-23-12'

Select 
parsename(replace(@data,'-','.'),1),
parsename(replace(@data,'-','.'),2),
parsename(replace(@data,'-','.'),3),
parsename(replace(@data,'-','.'),4)

Upvotes: 1

user7715598
user7715598

Reputation:

Sample Data 

    DECLARE @Table TABLE (QuizRecord    varchar(100))
    INSERT INTO @Table 
    SELECT '034-23-12' UNION ALL   
    SELECT '035-24-13' UNION ALL
    SELECT '036-25-14'

Query to split single column in multiple 

SELECT *
    ,REPLACE(SUBSTRING(QuizRecord, 0, CHARINDEX('-', QuizRecord)), 0, '') AS StudentID
    ,REPLACE(SUBSTRING(QuizRecord, CHARINDEX('-', QuizRecord), CHARINDEX('-', QuizRecord)), '-', '') AS CourseID
    ,REVERSE(SUBSTRING(REVERSE(QuizRecord), 0, CHARINDEX('-', REVERSE(QuizRecord)))) AS ModuleID
FROM @Table

Result

QuizRecord  StudentID   CourseID    ModuleID
--------------------------------------------
034-23-12    34             23          12
035-24-13    35             24          13
036-25-14    36             25          14

Upvotes: 0

Related Questions