asked on

Splitting the content of a column in SQL

I have a database with a table called "IN_Master" with a field called "strAlternateStock". (Image below). This is char field with numbers separated by a hyphen (-) as a delimiter. I need a script which would create a new table named "straltstk-split and then, if the content within the "stralternateStock" field is not "null", split the content into separate columns within that table.

The result would look something like this:

We are using SQL Server 2012

Hope that all makes sense.

Pawan Kumar

Hi Nathra,

Please try below- <<Just created a solution for you - TESTED in SQL 2012>>

--

CREATE TABLE IN_Master 
(
	strAlternateStock VARCHAR(100)
)
GO

INSERT INTO IN_Master VALUES ('12-34-56-78-88')
INSERT INTO IN_Master VALUES ('1-2-5')
INSERT INTO IN_Master VALUES('abc-my-pawan-k,')
INSERT INTO IN_Master VALUES('1-2')
GO

SELECT * FROM IN_Master 

--

Open in new window

Solution

--

;With CTE AS
(
    SELECT strAlternateStock,
    CAST(('<r><n>' + REPLACE(strAlternateStock,'-', '</n><n>') + '</n></r>') AS XML) X
   FROM IN_Master
   WHERE strAlternateStock IS NOT NULL
)
SELECT  strAlternateStock,
 i.value('n[1]','VARCHAR(50)') AS col1,
 i.value('n[2]','VARCHAR(50)') AS col2,
 i.value('n[3]','VARCHAR(50)') AS col3,
 i.value('n[4]','VARCHAR(50)') AS col4,
 i.value('n[5]','VARCHAR(50)') AS col5,
 i.value('n[5]','VARCHAR(50)') AS col6,
 i.value('n[7]','VARCHAR(50)') AS col7,
 i.value('n[8]','VARCHAR(50)') AS col8
FROM CTE c
CROSS APPLY c.X.nodes('/r') x(i)

--

Open in new window

Ouput

--

/*------------------------
OUPTUT
------------------------*/
strAlternateStock                                                                                    col1                                               col2                                               col3                                               col4                                               col5                                               col6                                               col7                                               col8
---------------------------------------------------------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------
12-34-56-78-88                                                                                       12                                                 34                                                 56                                                 78                                                 88                                                 88                                                 NULL                                               NULL
1-2-5                                                                                                1                                                  2                                                  5                                                  NULL                                               NULL                                               NULL                                               NULL                                               NULL
abc-my-pawan-k,                                                                                      abc                                                my                                                 pawan                                              k,                                                 NULL                                               NULL                                               NULL                                               NULL
1-2                                                                                                  1                                                  2                                                  NULL                                               NULL                                               NULL                                               NULL                                               NULL                                               NULL

(4 row(s) affected)


--

Open in new window

Hope it helps!

nathra

ASKER

If I am understanding your solution, which I may not be, I must have been unclear on something which I apologize for. Let me see if I can be more clear.

I already have a database with the Table "In_Master" in it. Within this table there is already the field call "strAlternateStock" which is a char field with data in it. The data within this field is separated by a hyphens, IE '12-34-56-78-88'. What I need do is to take the data from this field split it up and place it into columns in another Table using the hyphens as the delimiter for the split.

The result would be a new table named something like "AltStockSplit" with 8 columns and the data from the "strAlternateStock" field in the "In_Master" Table split and populating these columns.

I hope I have been clearer.

Thanks,

Pawan Kumar

Do you have any joining condition like ID or some key column?

nathra

ASKER

Are you talking between the In_Master table and the new table?

(You'll have to forgive me I am somewhat new to SQL.

ASKER CERTIFIED SOLUTION

Pawan Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

nathra

ASKER

Okay so I ran that in a debug in a query and it came back with multiple errors.

Pawan Kumar

What errors ?

Open a new SQL Query window in SSMS and Paste my last suggestion and execute. Do not debug.

Do you have AltStockSplit table in your database?

nathra

ASKER

Okay so that worked!

nathra

ASKER

Thanks so much. Now I just have to figure out how to keep the two tables in sync with each other with the In_Master being the main table that changes all the time. But that is a question for another day as it is almost 3am here! I will submit that one tomorrow.
Thanks for your help!

Pawan Kumar

Great ... Glad to help..

nathra

ASKER

Thanks for the help! Great expert help for this new novice to SQL!!