Split function with parsename-like functionality

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 2008 Forums

Transact-SQL (2008)

Split function with parsename-like functionality

Author

Topic

s_c
Starting Member

2 Posts

Posted - 2011-09-12 : 12:11:21

I'm not sure if that is exactly what I am after, it's just the best description I could come up with based on the table data I am looking at.

There is a description field in a SQL 2008 table that in most cases is delimited by a dash "-". The general rule is if there is a dash, there is always data following the dash. 2 dashes = 3 pieces of data, 3 dashes = 4 pieces of data, 4 dashes = 5 pieces of data.

I need to be able to write a function & pass it the delimiter to use, and to be able to return the string from (in this case) the next-to-last delimiter all the way to the end of the string, or, based on a switch I'd pass on the function call, any (or all) of the nodes to the left or the right of it.

Data example
This-Is-A-Test
This-Is-A-Test-Of-The-Emergency-Broadcasting-System
This-Is-Only-A-Test
This-Test-Is-Successful
This-Test-Stinks

(reasoning behind this is that based on another field on the table, the 3rd-from-the-end data would need to be returned, versus the next-to-last data. This is subject to change though).

The parsename function kinda does what I want it to, but it's limited to 4 chunks.

Not sure really of a good place to start, but I'm certain I'm not the first to have an issue like this. Any thoughts appreciated.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-09-12 : 12:35:48

Out of curiosity, is this some sort of import process or something? If not, then you should realy look at fixing your data structure.

As to your original question, you could use a parse function that has a ordinal position number and base the row you select based on the ordinal position. Or modify one to only return a row/value based on a certain position.

s_c
Starting Member

2 Posts

Posted - 2011-09-12 : 12:57:34

quote:
Out of curiosity, is this some sort of import process or something? If not, then you should realy look at fixing your data structure.

It's not on an import process. It's really re-purposing a table's contents for something that it wasn't originally intended for.

quote:
As to your original question, you could use a parse function that has a ordinal position number and base the row you select based on the ordinal position. Or modify one to only return a row/value based on a certain position.

That's what I've kept coming back to in terms of having it flexible enough to work as I wanted. I wasn't sure if there was a better way to do it.

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-12 : 13:15:10

see a parsing function here
http://visakhm.blogspot.com/2010/02/parsing-delimited-string.html

it can used like


SELECT t.Column,f.Val
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY t.Column ORDER BY f.ID DESC) AS Seq,*
FROM table t
CROSS APPLY dbo.ParseValues(t.Column,',')f
)t
WHERE Seq IN (2,3)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources