Jimmy_inc
asked on
How to Parse Comma-Separated Text into Multiple Fields using MS SQL Server
I need to achieve the same functionality described in http://support.microsoft.com/kb/95608 - method2 but for SQL Server 2008.
ASKER
Thanks that works but i need it to be able to split many CSV e.g 6
ASKER
Rather.. thi will be dynamic, depending on how many CSV are used, also it is likely this results will need to be joined to itself.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You can simple use bulk insert to import the csv data into your tables. This will split the data for you according to the delimiter you choose.
If you want to eidt the data or joins, you can insert into temporary tables too
CREATE TABLE #NAMES
(
ID INT,
FIRSTNAME VARCHAR(32),
LASTNAME VARCHAR(32)
)
BULK INSERT #NAMES
FROM 'c:\file.csv'
WITH
(
FIRSTROW = 2, --IF THE FIRST ROW IS COLUMN HEADERS
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
ASKER
Sorry mate I've been on holiday!!
I need to SQL which is compatible with SQL 2000 - SQL 2008 so is this a reason? why I am getting:
************************** ********** ********** ********
Server: Msg 170, Level 15, State 1, Line 9
Line 9: Incorrect syntax near 'max'.
************************** ********** ********** ********
I'm new to MS SQL Server
Can i step through the code or is there a way to use a locals window ?
thanks
I need to SQL which is compatible with SQL 2000 - SQL 2008 so is this a reason? why I am getting:
**************************
Server: Msg 170, Level 15, State 1, Line 9
Line 9: Incorrect syntax near 'max'.
**************************
I'm new to MS SQL Server
Can i step through the code or is there a way to use a locals window ?
thanks
ASKER
Also tigin44 I need the code to reference a field so am I to replace @text with the fields name?
I will try this...
I will try this...
DECLARE @text VARCHAR(MAX);
SET @text = 'Smith,John';
SELECT LEFT(@text, CHARINDEX(',', @text ) - 1) AS lastName,
RIGHT(@text, LEN(@text) - CHARINDEX(',', @text )) AS firstName