How to Split a Column into 2 Columns in SQL

Question

I have an SQL Query which extracts columns from a table that must be in this order

Item Id, Parms, Move list, MsgId. I also have a couple of requirements in the query that must remain there such as UserCode = automation and the Date is set to one day prior to the current day.

An example of the Parms output for one row is such

    CURRENT OUTPUT
    Parms:
    V51370,Move List,M-000001

--

    REQUIRED OUTPUT
    Media: v51370
    Comments: Was Removed From
    Move List: M-000001

What I want to do is split this Parms column into two separate columns that are in between the Item Id and Move list Column. The first Column should be called Media which extracts the first six characters from the Parms Column:v51370. The second column is comments and if the MsgId is 1 the comment should be: Was Removed from.

Can Anyone make any suggestions to a possible query I could test? I'm not sure where to fit the Parms column split in there. That would be greatly appreciated.

Please Note: I am using SQL Server Management Studio 2008, and the table name is JnlList.

     1 SAMPLE ROW (SELECT * FROM JNLLIST):
     ItemId: 2
     Date: 20122102.124652563
     Object: S-000001
     MsgID: 1
     Parms: V86143,Scratch List,S-000001
     Delimiter: -
     UserCode: Automation
     ActionId: 5
     List Type: S

could you show us what you have tried so far? and maybe some sample data from the table in question? — Dev N00B, Jun 28 '13 at 14:40
@DevN00B I have updated the question with sample data from the table. I hope this helps. — omar K, Jun 28 '13 at 15:02
The Parms column contains 3 commar seperated values, dont you therfore want this splitting into 3? — JsonStatham, Jun 28 '13 at 15:09
you can extract the first 6 charicters using something like SUBSTRING(Parms, 1, 6) AS Media — Dev N00B, Jun 28 '13 at 15:10
http://stackoverflow.com/questions/10581772/sql-server-2008-how-to-split-a-comma-separated-value-to-columns — JsonStatham, Jun 28 '13 at 15:13
or somthing like SELECT MID(column_name,start[,length]) AS Media FROM table_name; — Dev N00B, Jun 28 '13 at 15:13
@SelectDinstinct I've looked at many links on stack overflow, I'm having difficult putting the Case clause for the msgID while splitting that column up at the same time. — omar K, Jun 28 '13 at 15:18
@DevN00B Msg 2812, Level 16, State 62, Line 1 Could not find stored procedure 'length'. — omar K, Jun 28 '13 at 15:22
SELECT MID(Parms,1,6) AS Media FROM YOUR TABLE; The substring i posted earlier would also do this. The 1,6 refer to the number of char you want to select so start at 1 and select to 6 — Dev N00B, Jun 28 '13 at 15:29
@DevN00B Msg 195, Level 15, State 10, Line 1 'MID' is not a recognized built-in function name. — omar K, Jun 28 '13 at 15:34

score 0 · Accepted Answer · answered Jun 28 '13 at 15:48

0

to seperate the first 6 chars from the parms use

SELECT SUBSTRING(Parms,1,6) AS Media 
FROM YOUR TABLE;

Then you can use a subquery with the same syntax to select the rest of the chars from the Parms column and put them into a seperate column

answered Jun 28 '13 at 15:48

Dev N00B

218
1
8

How to Split a Column into 2 Columns in SQL

1 Answers1