CSV splitted in ROW'S

Question

CSV splitted in ROW'S

sqlfriends@sql.com

SSCommitted

Points: 1504
More actions
June 28, 2012 at 2:38 am

#262905

HI ;
How to split the csv values in rows , this is my sample string
UNIPAR LLC , IAE Power Products , MID-AM Equipment Inc.
, Diesel Electrical Equipment, Inc. , K & L Electronics Sales & Service, Inc.
, Logan Corporation , Morton Manufacturing Company , Hilliard Enterprises, Inc.
,PowerRail Distribution Inc. , Hatch & Kirk, Inc.
, Power Drives, Inc. , MAC Products, Inc. , Sterling Rail Inc. , Motive Power Resources, Inc.
, Saft America, Inc.
I want to split the comma separated vales and one condition is some of the companies having example
( Hatch & Kirk, Inc. , MID-AM Equipment Inc.) comma values with in the company name or nothing comma. we need to split the values
expected output ;
1.UNIPAR LLC
2. IAE Power Products
3. MID-AM Equipment Inc.
4. Diesel Electrical Equipment Inc. ,
5. K & L Electronics Sales & Service Inc.
6. Logan Corporation
7. Morton Manufacturing Company
8. Hilliard Enterprises Inc.
9. PowerRail Distribution Inc.
10 .Hatch & Kirk Inc.
11. Power Drives Inc.
12. MAC Products Inc.
13. Sterling Rail Inc.
14 . Motive Power Resources Inc.
15 . Saft America Inc.
any one help to solve the problem
Thanks
FA
DE

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

See this article, which explains how to do it --> http://www.sqlservercentral.com/articles/Tally+Table/72993/%5B/url%5D.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

rhythmk SSCertifiable Points: 7271 More actions · Answer 2

faijurrahuman17 (6/28/2012)
HI ;
How to split the csv values in rows , this is my sample string
UNIPAR LLC , IAE Power Products , MID-AM Equipment Inc.
, Diesel Electrical Equipment, Inc. , K & L Electronics Sales & Service, Inc.
, Logan Corporation , Morton Manufacturing Company , Hilliard Enterprises, Inc.
,PowerRail Distribution Inc. , Hatch & Kirk, Inc.
, Power Drives, Inc. , MAC Products, Inc. , Sterling Rail Inc. , Motive Power Resources, Inc.
, Saft America, Inc.
I want to split the comma separated vales and one condition is some of the companies having example
( Hatch & Kirk, Inc. , MID-AM Equipment Inc.) comma values with in the company name or nothing comma. we need to split the values
expected output ;
1.UNIPAR LLC
2. IAE Power Products
3. MID-AM Equipment Inc.
4. Diesel Electrical Equipment Inc. ,
5. K & L Electronics Sales & Service Inc.
6. Logan Corporation
7. Morton Manufacturing Company
8. Hilliard Enterprises Inc.
9. PowerRail Distribution Inc.
10 .Hatch & Kirk Inc.
11. Power Drives Inc.
12. MAC Products Inc.
13. Sterling Rail Inc.
14 . Motive Power Resources Inc.
15 . Saft America Inc.
any one help to solve the problem
Thanks
FA
DE

Try out this logic in a UDF.

Use Charindex and Substring function to find the position of comma and split the statement and store splitted values in a table variable

--rhythmk
------------------------------------------------------------------
To post your question use below link

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂

Abhijeet Chavan SSCommitted Points: 1511 More actions · Answer 3

Hi

You can use the following query:

DECLARE @CSV_String VARCHAR(2000)

SET @CSV_String = 'UNIPAR LLC , IAE Power Products , MID-AM Equipment Inc.

, Diesel Electrical Equipment, Inc. , K & L Electronics Sales & Service, Inc.

, Logan Corporation , Morton Manufacturing Company , Hilliard Enterprises, Inc.

,PowerRail Distribution Inc. , Hatch & Kirk, Inc.

, Power Drives, Inc. , MAC Products, Inc. , Sterling Rail Inc. , Motive Power Resources, Inc.

, Saft America, Inc.

'

;WITH CTE AS

(

SELECT 1 AS ID, SUBSTRING(@CSV_String, 1, CHARINDEX(',', @CSV_String, 1)-1) AS Col1, CHARINDEX(',', @CSV_String, 1)+1 AS START

UNION ALL

SELECT ID + 1, SUBSTRING(@CSV_String, START, ISNULL(NULLIF(CHARINDEX(',', @CSV_String, START), 0)-START, 2000)), ISNULL(NULLIF(CHARINDEX(',', @CSV_String, START), 0)+1, 0)

FROM CTE

WHERE START <> 0

)

SELECT ID, LTRIM(RTRIM(Col1)) FROM CTE

Jeff Moden SSC Guru Points: 1004177 More actions · Answer 4

Abhijeet Chavan (6/28/2012)
Hi
You can use the following query:
DECLARE @CSV_String VARCHAR(2000)
SET @CSV_String = 'UNIPAR LLC , IAE Power Products , MID-AM Equipment Inc.
, Diesel Electrical Equipment, Inc. , K & L Electronics Sales & Service, Inc.
, Logan Corporation , Morton Manufacturing Company , Hilliard Enterprises, Inc.
,PowerRail Distribution Inc. , Hatch & Kirk, Inc.
, Power Drives, Inc. , MAC Products, Inc. , Sterling Rail Inc. , Motive Power Resources, Inc.
, Saft America, Inc.
'
;WITH CTE AS
(
SELECT 1 AS ID, SUBSTRING(@CSV_String, 1, CHARINDEX(',', @CSV_String, 1)-1) AS Col1, CHARINDEX(',', @CSV_String, 1)+1 AS START
UNION ALL
SELECT ID + 1, SUBSTRING(@CSV_String, START, ISNULL(NULLIF(CHARINDEX(',', @CSV_String, START), 0)-START, 2000)), ISNULL(NULLIF(CHARINDEX(',', @CSV_String, START), 0)+1, 0)
FROM CTE
WHERE START <> 0
)
SELECT ID, LTRIM(RTRIM(Col1)) FROM CTE

Read the article that Cadavre pointed out and see why that might not be such a good idea.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)