Splitting single Field into multiple fields via SELECT 1

ousoonerjoe · Feb 19, 2008

I have found several different ways to do this at the table level, but just need to adjust the output of a SELECT statement. I have an address field that is "CHAR(13)+CHAR(10)" delimited. It could have anywhere from 3 to 7 items and I need to break it down to max of 4. I'd rather do this in the statement rather than through the app (makes future changes easier). VB6 has a function called "Split" that makes this easy, however, I do not know of a function in SQL 2000 to accomplish the same result.

Sample data:

Name1 (always exist)
Name2 (may exist)
Attn: Name3 (may exist)
Street1 (always exist)
Street2 (may exist)
City, ST Zip (always exist)

Any assistance or guidance is appreciated. Thank you.

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

SQLSister · Feb 19, 2008

http://msdn2.microsoft.com/en-us/library/aa496058.aspx

"NOTHING is more important in a database than integrity." ESquared

bborissov · Feb 19, 2008

I stole this from gmastros.:

Code:

declare @test varchar(8000)
SET @test = 'This is something I have in mind'+CHAR(13)+CHAR(10)+'But I am not sure if this will work'
SELECT PARSENAME(REPLACE(@test,CHAR(13)+CHAR(10),'.'),2) AS Str1,
       PARSENAME(REPLACE(@test,CHAR(13)+CHAR(10),'.'),1) AS Str2

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

gmmastros · Feb 19, 2008

Boris,

ParseName is 'cheating'. I have no problem using it, but only under tightly controlled circumstances. ParseName fails when there are more than 4 parts.

Code:

[COLOR=blue]Declare[/color] @Data [COLOR=blue]varchar[/color](100)

[COLOR=blue]Set[/color] @Data = [COLOR=red]'a.b.c.d'[/color]

[COLOR=blue]Select[/color] Parsename(@Data, 1) [COLOR=green]-- Returns d
[/color]
[COLOR=blue]Set[/color] @Data = [COLOR=red]'a.b.c.d.e'[/color]

[COLOR=blue]Select[/color] Parsename(@Data, 1) [COLOR=green]-- Returns NULL[/color]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

bborissov · Feb 19, 2008

I forgot. That will work only up to 4 level split

If you have only ONE or none CHAR(13)+CHAR(10) sequence it will work. But if you have more that this and you are not sure how many you have DO NOT use this method.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

philhege · Feb 19, 2008

Don't you just HATE fields formatted like that?

I'd lobby for a redesign, but if your app is anything like ours, that field is deeply embedded.

Data presentation logic belongs in your process component, not the data access component. You should be able to code a centralized, inheritable class to take care of this.

If not, then, well....

Create a user defined function that accepts the field as input and returns a single-row table variable.

More to follow, if I have time.

Phil H.
-----------
A virtual machine ate my accounting data. I transferred it to a physical box, then I beat it to smithereens with a sledgehammer. I feel better.

gmmastros · Feb 19, 2008

I recently wrote this to help me clean up some data. I suspect the performance here would be awful, but for cleaning up data, that's not terribly important.

First, create this split function:

Code:

[COLOR=blue]ALTER[/color] [COLOR=#FF00FF]Function[/color] [dbo].[Split]
	(
	@CommaDelimitedFieldNames [COLOR=blue]Varchar[/color](8000), 
	@CharToFind [COLOR=blue]VarChar[/color](10) 
	) 
Returns @Tbl_FieldNames [COLOR=blue]Table[/color] ([COLOR=blue]Position[/color] [COLOR=blue]Integer[/color] [COLOR=blue]Identity[/color](1,1), FieldName [COLOR=blue]VarChar[/color](8000)) [COLOR=blue]As[/color] 
[COLOR=blue]Begin[/color] 
 [COLOR=blue]Set[/color] @CommaDelimitedFieldNames = @CommaDelimitedFieldNames + @CharToFind
 [COLOR=blue]Declare[/color] @Pos1 [COLOR=blue]Int[/color]
 [COLOR=blue]Declare[/color] @pos2 [COLOR=blue]Int[/color]
 
 [COLOR=blue]Set[/color] @Pos1=1
 [COLOR=blue]Set[/color] @Pos2=1
 [COLOR=blue]While[/color] @Pos1<[COLOR=#FF00FF]DataLength[/color](@CommaDelimitedFieldNames)
 [COLOR=blue]Begin[/color]
 [COLOR=blue]Set[/color] @Pos1 = [COLOR=#FF00FF]CharIndex[/color](@CharToFind, @CommaDelimitedFieldNames,@Pos1)
 [COLOR=blue]Insert[/color] @Tbl_FieldNames [COLOR=blue]Select[/color] [COLOR=#FF00FF]Cast[/color]([COLOR=#FF00FF]Substring[/color](@CommaDelimitedFieldNames,@Pos2,@Pos1-@Pos2) [COLOR=blue]As[/color] [COLOR=blue]VarChar[/color](8000))
 [COLOR=blue]Set[/color] @Pos2=@Pos1+1
 [COLOR=blue]Set[/color] @Pos1 = @Pos1+1
 [COLOR=blue]End[/color] 
 [COLOR=blue]Return[/color]
[COLOR=blue]End[/color]

And then create this ParsePart function:

Code:

[COLOR=blue]ALTER[/color] [COLOR=#FF00FF]Function[/color] [dbo].[ParsePart]
	(
	@Data [COLOR=blue]varchar[/color](8000),
	@Delimiter [COLOR=blue]VarChar[/color](8000),
	@Index [COLOR=blue]Int[/color]
	)
Returns [COLOR=blue]VarChar[/color](8000)
[COLOR=blue]As[/color]
[COLOR=blue]Begin[/color]
	[COLOR=blue]Return[/color] ([COLOR=blue]Select[/color] FieldName [COLOR=blue]From[/color] dbo.Split(@Data, @Delimiter) [COLOR=blue]Where[/color] [COLOR=blue]Position[/color] = @Index)
[COLOR=blue]End[/color]

Now you can use this for more than 4 parts, like this...

Code:

[COLOR=blue]Declare[/color] @Data [COLOR=blue]varchar[/color](100)

[COLOR=blue]Set[/color] @Data = [COLOR=red]'a.b.c.d'[/color]

[COLOR=blue]Select[/color] dbo.ParsePart(@Data, [COLOR=red]'.'[/color], 1)

[COLOR=blue]Set[/color] @Data = [COLOR=red]'a.b.c.d.e.f.g'[/color]

[COLOR=blue]Select[/color] dbo.ParsePart(@Data, [COLOR=red]'.'[/color], 1),
       dbo.ParsePart(@Data, [COLOR=red]'.'[/color], 2),
       dbo.ParsePart(@Data, [COLOR=red]'.'[/color], 3),
       dbo.ParsePart(@Data, [COLOR=red]'.'[/color], 4),
       dbo.ParsePart(@Data, [COLOR=red]'.'[/color], 5),
       dbo.ParsePart(@Data, [COLOR=red]'.'[/color], 6),
       dbo.ParsePart(@Data, [COLOR=red]'.'[/color], 7),
       dbo.ParsePart(@Data, [COLOR=red]'.'[/color], 8),
       dbo.ParsePart(@Data, [COLOR=red]'.'[/color], 9)

ParsePart will return NULL if there isn't a corresponding element in the 'array'. What I like about this solution is that the delimiter is in the parameter of the function so you don't need to bother with the replace function.

Feel free to use this code, but, like I said earlier, the performance may not be acceptable.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

bborissov · Feb 19, 2008

George,
I knew that, but I forgot to mention it in my first answer.
I tried to correct my answer after a while but it get worse.
I use this (as you said under controlled circumstances) but I forgot them when I post my answer.

ousoonerjoe, sorry!

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

ousoonerjoe · Feb 19, 2008

Thanks, gmmastros. Once again, you've proved major assistance.

Now to figure out the logic of nailing down the moving target

And i learned something else... in all my years of working with SQL 2000, i have never seen Functions used until SQL2005. Something new to research and add to my repertoire of tools for SQL2000. Will they convert interchangeably between 2000 and 2005?

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

gmmastros · Feb 19, 2008

yes.

Any sql2000 function will work with sql2005. sql2005 allows you to do more with functions than 2000 will, so you cannot always run a 2005 function on a 2000 server.

Please do not get too excited about functions. Functions are notorious for being slow. So, it's best to use them sparingly.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

ousoonerjoe · Feb 19, 2008

Understood. But for simple 5 AM automation apps that time and overhead is not an issue for, they're perfect. I can see where they could cause problems, but this is for a daily file extract that runs when no one is home. Thanks for the warning.

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Splitting single Field into multiple fields via SELECT 1

ousoonerjoe

Programmer

SQLSister

Programmer

bborissov

Programmer

gmmastros

Programmer

bborissov

Programmer

philhege

Programmer

gmmastros

Programmer

bborissov

Programmer

ousoonerjoe

Programmer

gmmastros

Programmer

ousoonerjoe

Programmer

Similar threads

Part and Inventory Search

Sponsor