• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1391
  • Last Modified:

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.
0
Jimmy_inc
Asked:
Jimmy_inc
  • 4
  • 2
1 Solution
 
tigin44Commented:
use built in SQL function like

DECLARE @text      VARCHAR(MAX);
SET @text = 'Smith,John';
SELECT LEFT(@text, CHARINDEX(',', @text ) - 1) AS lastName,
      RIGHT(@text, LEN(@text) - CHARINDEX(',', @text )) AS firstName
0
 
Jimmy_incAuthor Commented:
Thanks that works but i need it to be able to split many CSV e.g 6
0
 
Jimmy_incAuthor Commented:
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.
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
tigin44Commented:
this may help you


DROP TABLE Sample
CREATE TABLE Sample(
	ID		int,
	someVal varchar(10),
	anotVal varchar(10)
);


DECLARE @text	VARCHAR(MAX);
DECLARE @numCols int;
DECLARE @i		int;
DECLARE @col1	int;
DECLARE @col2	varchar(10);
DECLARE @col3	varchar(10);

SET @text = '1,aaa,aba,2,bbb,bab,3,ccc,cac,4,ddd,dad,'
SET @numCols = 3;
SET @i = 1

WHILE LEN(@text) > 0
BEGIN
select @i, @text, LEFT(@text, CHARINDEX(',', @text ) - 1) 
	IF @i = 1 
		SELECT @col1 = CAST(LEFT(@text, CHARINDEX(',', @text ) - 1) AS int) 
	IF @i = 2 
		SELECT @col2 = LEFT(@text, CHARINDEX(',', @text )-1) 
	IF @i = 3 
		SELECT @col3 = LEFT(@text, CHARINDEX(',', @text )-1) 

	SET @i = @i + 1;
	
	IF @i-1 = @numCols
	BEGIN
		INSERT INTO Sample
		VALUES(@col1, @col2, @col3);
		SET @i = 1;
	END	
	SELECT @text = RIGHT(@text, LEN(@text) - CHARINDEX(',', @text ))
END

select * from Sample

Open in new window

0
 
Ephraim WangoyaCommented:

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' 
    )

Open in new window

0
 
Jimmy_incAuthor Commented:
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



0
 
Jimmy_incAuthor Commented:
Also tigin44 I need the code to reference a field so am I to replace @text with  the fields name?
I will try this...
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now