Still trying to figure a UDF to do this

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

SQL Server Development (2000)

Still trying to figure a UDF to do this

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2004-03-18 : 23:51:26

I have a table with a column of type VARCHAR. I want to parse the column into 4 VARCHAR values.

It would be great to do this with a single function or procedure since the parsing logic is context sensitive (e.g. You gotta find Token1 before you can find Token2, etc..). Inefficent to use 4 separate UDFs in this case.

I've thought myself into a box using a function that returns a single row with 4 values. It works well like:

SELECT * from MyUDF('Mytexthere') -- But there's no way to specify a column of another table as an argument.

What I'd like to end up with is:


Select MyText, Token1, Token2, Token3, Token4
  FROM MyTable

Any ideas that would help with this problem appreciated. I think I'm gonna start working on the 4 UDFs meanwhile.

Sam

SamC
White Water Yakist

3467 Posts

Posted - 2004-03-18 : 23:56:23

Another idea was a single UDF that returns 4 delimited tokens in a single VARCHAR string.

I'm not aware of any really simple way to split apart tokens in SQL like the ASP SPLIT function. I am aware of the techniques used to parse CSV strings using Tally tables.

Sam

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2004-03-19 : 00:04:53

Have you looked at the PARSENAME function?? I believe Merkin did an article...

And you cannot use columns in table valued functions.. Yukon can I believe, with the APPLY and CROSS APPLY keyowrds....

DavidM

"An Ugg Boot is a generic Australian term that has been in use for nearly 100 hundred years. Now some coporate wanker has trademarked it.. "

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2004-03-19 : 01:59:25

Yes, PARSENAME() might be useful in this situation.
Maybe this one
http://www.users.drew.edu/skass/sql/FindFeatures.sql.txt (Oops, Steve Kass again

)
or this
http://www.sommarskog.se/arrays-in-sql.html
or this
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag01/html/treatyourself.asp

Hm...more than one way to skin that cat, I'd say. Maybe there is something useful for you.

--Frank
http://www.insidesql.de

SamC
White Water Yakist

3467 Posts

Posted - 2004-03-19 : 08:23:44

One or more of my Tokens uses "." which makes Parsename dicey.

I ended up writing a single function (easier to debug), with an extra parameter "N" which specifies which Token(N) to return.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources