473,323 Members | 1,589 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,323 software developers and data experts.

Splitting a field into multiple rows

Hopefully this is an easy process, but it is giving me all kinds of troubles.

I am working in MS SQL 2005. I need to take a field that has multiple objects (text) in there that is separated by commas.
Example

Field Value
ID 223
lt LTDLANDSCP,LTDWTR-FRN,LTDWTR-VIE
ext EXFGRILL,EXFGUTTERS


Each field needs to be split into multiple rows so that I can point it at another table structured below to return the groupid and numid. I am inserting only the groupid and numid into a final results table.

groupid numid shortdes
1 1 LTDLANDSCP
1 2 LTDWTR-FRN
1 3 LTDWTR-VIE
2 1 EXFGRILL
2 2 EXFGUTTERS
etc

Hopefully this makes sense. Any help is appreciated.
Apr 4 '07 #1
8 6119
iburyak
1,017 Expert 512MB
Hope it helps somewhat.

1. Create following function

[PHP]CREATE FUNCTION fnSplit(@String nvarchar(4000), @Delimiter char(1))
RETURNS @Results TABLE (ID int identity(1,1), Items nvarchar(4000))
AS

BEGIN
DECLARE @INDEX INT
DECLARE @SLICE nvarchar(4000)

SELECT @INDEX = 1
WHILE @INDEX !=0


BEGIN
-- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER
SELECT @INDEX = CHARINDEX(@Delimiter,@STRING)
-- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE
IF @INDEX !=0
SELECT @SLICE = LEFT(@STRING,@INDEX - 1)
ELSE
SELECT @SLICE = @STRING
-- PUT THE ITEM INTO THE RESULTS SET
INSERT INTO @Results(Items) VALUES(@SLICE)
-- CHOP THE ITEM REMOVED OFF THE MAIN STRING
SELECT @STRING = RIGHT(@STRING,LEN(@STRING) - @INDEX)
-- BREAK OUT IF WE ARE DONE
IF LEN(@STRING) = 0 BREAK
END
RETURN
END [/PHP]

2. Execute statemetn below.

[PHP]select *
from fnSplit('LTDLANDSCP,LTDWTR-FRN,LTDWTR-VIE', ',')[/PHP]


You still need some programming but it is the main stone to start from.

Good Luck.
Apr 4 '07 #2
Hey thanks alot for that. It worked just as planned.

Now I have added a little more from my select statement because I have multiple fields that I need to pull from. Let me know if this is not the desired way to do it.

I have say 3 fields...
Interior, Exterior, Floors

within each of these, I have
xyz, abc, def

I am using
SELECT (Interior + ',' + Exterior + ',' + Floors) FROM tbl
to get a single string with commas between all but the last value.

I have placed it in the
SELECT * FROM fnSplit and it works great (almost)

If I have a NULL value in one of the columns, it returns a single NULL value without any other splits. If there are values with each one, it returns the split perfectly.

Do I need to use an IF statement on each field to make sure it is NOT NULL before adding it to the string? If so, is there a suggested way of making this work?

Thanks!
Apr 4 '07 #3
iburyak
1,017 Expert 512MB
Do following :



[PHP]SELECT isnull(Interior,'') + ',' + isnull(Exterior,'') + ',' + isnull(Floors,'') FROM tbl[/PHP]

Good Luck.
Apr 4 '07 #4
iburyak
1,017 Expert 512MB
Don't forget that Split function has second parameter which is delimiter.

I wrote it for you not sure which delimiter you'll have in each string.... :)

Never concatenate string with NULL it will always be null.
Apr 4 '07 #5
You are great. Thanks again for everything. I believe I have it working just as I need to.
Apr 4 '07 #6
One last question on this same idea...

When I pull my information in with the isnull() syntax, the string it created properly. That works great.

Is there anyway to have those fields just dumped versus pulling them into the string as ' ' ? The return set gives me some NULL values that I would like to get removed. I know can filter it in my display query, however just for curiousity sake, is there a way to basically skip that field if it is NULL?

Thanks in advance!
Apr 5 '07 #7
iburyak
1,017 Expert 512MB
I am not sure if I understood you correctly.
In example:


[html]xyz, null, def[/html]

You want to get


[html]xyz, def [/html]

?
Apr 5 '07 #8
Say...
Field1 = abc,def,ghi
Field2 = NULL
Field3 = xyz, 123

Currently it is producing

ID Item
1 abc
2 def
3 ghi
4 NULL
5 xyz
6 123

I want to be able to not produce the ID 4 with the NULL value. So the end result would be...

ID Item
1 abc
2 def
3 ghi
4 xyz
5 123

Does that make more sense hopefully?
Apr 5 '07 #9

Sign in to post your reply or Sign up for a free account.

Similar topics

7
by: Rick Caborn | last post by:
Does anyone know of a way to execute sql code from a dynamically built text field? Before beginning, let me state that I know this db architecture is built solely for frustration and I hope to...
1
by: Andy Britcliffe | last post by:
Hi I'm faced with the situation where I could have a single physical file that could contain multiplie XML documents e.g file.txt contains the following: <?xml version="1.0"...
13
by: James Conrad St.John Foreman | last post by:
One of the larger tables in our database is now 6.8 million rows (1 per financial transaction since 2000). Every time an amendment is made to a booking, new rows are added to the table for each...
9
by: Dr. StrangeLove | last post by:
Greetings, Let say we want to split column 'list' in table lists into separate rows using the comma as the delimiter. Table lists id list 1 aa,bbb,c 2 e,f,gggg,hh 3 ii,kk 4 m
1
by: Hennie7863 | last post by:
Hi i want to create a table as follows : if exists (select * from dbo.sysobjects where id = object_id(N'') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table GO Create table...
2
by: cynthiav | last post by:
Hello, I am working on a query in Access 2003. I have multiple rows of data like below: Ord # date description time 1 11/7/06 chocolate ...
5
by: mctime | last post by:
Hello, I am attempting to split a raw data table into a new table that has split out a specific field in the raw data and created a new record for each split but I have come to an impasse due to...
1
by: Nevgar | last post by:
I am fairly new to php / MySQL and have a problem with a query and an insert. I have a database that maybe isn't the best design - it includes a field "Former Names" which is varchar 255. It...
1
by: chris f | last post by:
I'm dynamically populating a Table control in ASP.NET 2. Each row has 4 columns but column #3 needs to be split into 3 rows and column #4 needs to be split into 4 rows. Each of these cells contains...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.