PARSENAME Function: WHY Does this Example Work?? 1

[kjv1611]

This may sound silly, and I'm not one to complain when something works, but I just don't get how this one works.

Here's the example someone posted in a blog at sqlteam.com:

http://www.sqlteam.com/article/using-the-parsename-function-to-split-delimited-data

What I don't get is that in his real world example, about 3/4 way down the page, he uses values like this:

Create Table OldData (
        recordID int,
        startDate datetime,
        duration char(11)
)
GO

INSERT INTO OldData (recordID, startDate, duration)
VALUES (1, '2002-11-10 12:00:00.000', '4:15:03:24')

INSERT INTO OldData (recordID, startDate, duration)
VALUES (2, '2002-11-10 12:00:00.000', '12:1:23:2')


GO

So what I don't get is how he's able to use REPLACE inside PARSENAME to get to the end result. I know that REPLACE replaces one character or string with another, but what I don't get is how he's able to:
Use a string that already has periods/decimals in it
Replace the colon character with period/decimal
And yet still accurately use PARSENAME

Or does it just happen that the trailing period just gets whacked off? I think that may be what's happening, but not sure. Could anyone glance at this and let me know if I'm being accurate or not?

The reason I'm asking is that if I tried to use this in a real world scenario where I needed to introduce decimals, but I already HAVE decimals in the value, I'd end up messing up the data. Frankly, since much of the data I work with includes dollar amounts, chances are it'll already include decimals.

And all that said, are there any newer/better functions for splitting strings in SQL other than writing your own function?

I did come across this thread where it looks like gmmastros wrote a great function to work with:

http://www.tek-tips.com/viewthread.cfm?qid=1336767

Thanks for any thoughts/suggestions.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[fredericofonseca]

if your string already has periods, and you wish to replace other chars with periods also without "messing up" you need to first replace the original periods with another char that is not used within your strings (for example ^), replace the other chars with periods , do the parsename, and then replace your replacement char with periods so the original ones remain as they were

e.g.
declare @str varchar(30) = '2002-11-10 12:03:04.000'

declare @workvar varchar(30) = replace(replace(replace(@str,'.','^'),':','.'),' ','.')

declare @p1 varchar(30)
declare @p2 varchar(30)
declare @p3 varchar(30)
declare @p4 varchar(30)
set @p1 = replace(parsename(@workvar,1),'^','.')
set @p2 = replace(parsename(@workvar,2),'^','.')
set @p3 = replace(parsename(@workvar,3),'^','.')
set @p4 = replace(parsename(@workvar,4),'^','.')

select @p1
select @p2
select @p3
select @p4


but I would not normally use this function to split things.

either use the split function supplied with Master Data Services if I need to split based on several delimiters, or I use a version of

http://www.sqlservercentral.com/articles/Tally+Table/72993/

or

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=153458

or even another one I don't have handy now.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[kjv1611]

Thanks! I'll take a look at the options you mentioned. If you happen to remember the other, and don't mind pasting/sharing, that'd be great as well.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[TamarGranor]

In the example you cited, he's applying ParseName() only to the Duration field, which has colons, but no periods. He's thus able to add the duration to the start date to get the end date.

Tamar