SQL Newbie needs to Split data

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SQL Newbie needs to Split data

Author

Topic

Trebz
Starting Member

7 Posts

Posted - 2004-12-03 : 09:10:39

Hi

I'm pretty new to the more advanced SQL stuff (I come from an ASP background where SELECT * FROM tblMyTable is acceptable!)

I am currently trying to split a tab delimited, crlf seperated 'spreadsheet' of data.

I have a split function that basically takes some text and a delimiter and returns the result, for example.

If i do

SELECT * FROM dbo.SplitFunction('a,b,c,d', ',')

I get

an example of what i need to split up is


this    is    a    tab    delimited    column
its    rows   are  also   split        by
crlf   xxx    xxx  xxx    xxx          xxxx

So what i thought was to first split the rows, and then split each row into it's columns and insert the lot into a table, this is what I have so far.


DROP TABLE #RMB1
GO
DROP TABLE #RMB2
GO
CREATE TABLE #RMB1
(
	MyCol1	varchar(500),
)
CREATE TABLE #RMB2
(
	MyCol1	varchar(50),
	MyCol2	varchar(50),
	MyCol3	varchar(50),
	MyCol4	varchar(50),
	MyCol5	varchar(50)
)

DECLARE
	@mytxt varchar(8000),
	@delimiter varchar(10),
	@delimiter2 varchar(10)

SELECT
	@mytxt = 'hello	world	i	am	tab	delimited
this	is	also	row	delimited	with
carriage	return	line	feed	combination	characters',
	@delimiter = CHAR(13) + CHAR(10),
	@delimiter2 = CHAR(9)

INSERT INTO #RMB1(MyCol1)
	SELECT Value FROM dbo.splitfunc(@mytxt, @delimiter)

INSERT INTO #RMB2(MyCol1, MyCol2, MyCol3, MyCol4, MyCol5)
	SELECT * FROM dbo.splitfunc('ROWS FROM #RMB1', @delimiter2)

SELECT * FROM #RMB2

Where I am lost is where i wrote 'ROWS FROM #RMB1', what i want to do is for each row in RMB1, split it into columns, and insert each column into table #RMB2

Am i even heading in the right direction?

Any thoughts appreciated.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-12-03 : 09:31:56

Take a look here:

http://www.sqlteam.com/item.asp?ItemID=2652

You can use this technique to give you the multiple rows, and use the function to split the columns.

BTW, if you're getting this data from a file, bcp and BULK INSERT can import them very handily.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-12-03 : 09:34:12

i don't think you need two splits:

I used by split function, which i can supply, but it looked like you already had one


DECLARE
	@mytxt varchar(8000),
	@delimiter varchar(10),
	@delimiter2 varchar(10)

SELECT
	@mytxt = 'hello	world	i	am	tab	delimited
this	is	also	a	row	delimited	with
carriage	return	line	feed	combination	characters',
	@delimiter = CHAR(13) + CHAR(10),
	@delimiter2 = CHAR(9)

Set @myTxt = Replace(@myTxt,@delimiter,@delimiter+@delimiter2)

Create Table #tempSplit (id int, data varchar(100), endOfRow bit)

Insert into #tempSplit
Select 
	id, Data, endOfRow = case when data like '%'+@delimiter+'%' then 1 else 0 end
From web.dbo.split(@myTxt,@delimiter2)


Create Table #tempParsed (id int, data varchar(100), endOfRow bit, lastEnd int, row int, col int)

Insert into #tempParsed
Select
	id, data, endOfRow, LastEnd, 
	row = (id - 1 - lastEnd)/5, 
	col = (id - 1 - lastEnd)%5
From
	(
	Select 
		id, 
		data, 
		endOfRow, 
		lastEnd = isnull((Select max(id) from #tempSplit where id < A.id and endOfRow=1),0)
	From #tempSplit A
	) Z

Select 
	col1 = (Select data From #tempParsed where lastEnd = A.LastEnd and row = A.row and col=0),
	col2 = (Select data From #tempParsed where lastEnd = A.LastEnd and row = A.row and col=1),
	col3 = (Select data From #tempParsed where lastEnd = A.LastEnd and row = A.row and col=2),
	col4 = (Select data From #tempParsed where lastEnd = A.LastEnd and row = A.row and col=3),
	col5 = (Select data From #tempParsed where lastEnd = A.LastEnd and row = A.row and col=4) 
From
	(Select distinct lastEnd, row From #tempParsed) A

Corey

Trebz
Starting Member

7 Posts

Posted - 2004-12-03 : 09:50:33

Thank you very much for the code above, thats almost what I want, except I get this as the result...


hello	world	i	am	tab
delimited  	NULL	NULL	NULL	NULL
this	is	also	row	delimited
with  	NULL	NULL	NULL	NULL
carriage	return	line	feed	combination
characters	NULL	NULL	NULL	NULL

and I want


hello		world	i	am	tab		delimited
this		is	also	row	delimited	with
carriage	return	line	feed	combination	characters

Can't seem to figure out where it's screwing up...it does okay at the start, it works out the rows, as you can see.

Trebz
Starting Member

7 Posts

Posted - 2004-12-03 : 09:53:20

Sorr, forget that, had wrong number of cols :-) Thank you thank you thank you :-)

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-12-03 : 10:23:04

your welcome!

Corey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources