SQL String parse comma separated

I am reading a text file into a temp table and it's just 1 long line separated by commas.  I would like to parse this string into separate fields.  How can I do that with a function or sql script?

Example:

ECON,2992,10/11/2013,6:00AM,20 S
MATH,29332,10/11/2013,90 T
FIN,3293,10/11/2013,8:00AM,20 T,ADVANCE

I may have to do multiple different parse and put into a temp table since some line have more fields then others.  If I can parse the above regardless of how many commas into a define table, I should be able to do the rest.  Just having issue getting it to parse.

Example output
holemaniaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Surendra NathTechnology LeadCommented:
A bcp command or bulk insert is the one you are look for?

And the number of fields or columns in each row should be consistent for bcp to work, so just incase if you dont need to insert anything for note you need place an empty commad (,) as shown below

ECON,2992,10/11/2013,6:00AM,20 S,
MATH,29332,10/11/2013,90 T,
FIN,3293,10/11/2013,8:00AM,20 T,ADVANCE
0
holemaniaAuthor Commented:
Is there a way to parse if it's already read into 1 string field?  I can filter it by dept and parse each line since it's consistent with the number of commas.  From there, then I can do a union all.  What's best way to parse by comma as separator if it's already 1 long string in a temp table?
0
SharathData EngineerCommented:
I prefer to bulk insert the data from the flat file to a table with one column. So you will have all comma separated data in one row. From that table, you can parse the data into multiple fields into another table. Let me know if you are looking for a solution in these lines.
0
Scott PletcherSenior DBACommented:
Here's a high-performing function to split the string, using an inline table-valued function.

A sample invocation is shown as part of the function code.



CREATE FUNCTION [dbo].[SplitStringIntoTable]  (
    @string varchar(8000),
    @delimiter varchar(5)
    )
RETURNS TABLE WITH SCHEMABINDING
AS
--SELECT * FROM (SELECT 'ab,c,def,ghijklm,no,prq,,,,,st,u,,' AS string) AS test_table CROSS APPLY dbo.SplitStringIntoTable(test_values.string, ',')
RETURN
--Inline-CTE-Driven "tally table" produces values from 0 to 9999  ... enough to cover varchar(8000)
WITH cteDigits AS (
    SELECT 0 AS digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
    SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
),
cteTally AS (
    SELECT [1000s].digit * 1000 + [100s].digit * 100 + [10s].digit * 10 + [1s].digit AS num
    FROM cteDigits [1s] --0 thru 9
    CROSS JOIN cteDigits [10s] --thru 99
    CROSS JOIN cteDigits [100s] --thru 999
    CROSS JOIN cteDigits [1000s] --thru 9999
),
cteBase(num) AS ( --Limit the number of rows up front, for both a performance gain and prevention of accidental "overruns"
        SELECT 0 UNION ALL
        SELECT TOP (DATALENGTH(ISNULL(@string, 1))) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM cteTally
),
cteStart(num1) AS ( --This returns num + 1 (starting position of each "value" just once for each delimiter)
        SELECT b.num + 1
        FROM cteBase b
        WHERE (SUBSTRING(@string, b.num, 1) = @delimiter OR b.num = 0)
)
--Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
SELECT ROW_NUMBER() OVER(ORDER BY s.num1) AS value_seq,
       LTRIM(RTRIM(SUBSTRING(@string, s.num1, ISNULL(NULLIF(CHARINDEX(@delimiter, @string, s.num1), 0) - s.num1, 8000)))) AS value
FROM cteStart s;

GO
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.