December 11, 2009 at 10:38 am
I have a table (called dbProfile) which always contains just 1 record (imported from configuration record in another system)
the table has a field (kPEAccountCode) which contains a delimited list e.g. (A0001,A0002,B0003 etc)
I want to be able to split this field into a new table (either static or as a function), e.g.
kPEAccountCode
----------------
A0001
A0002
B0003
etc
I have found lots of split functions on the net, but cannot seem to implement any one of them (they typically give an example of use by using literal strings as an input)
I want to be to provide my field and delimiter as an input and for a function (or any solution!) to return a table as a result.
Part of the problem is that I cannot grasp how to implement Table UDFs. I can call them by passing a literal string, e.g.
Select * From MY_FUNCTION('A0001,A0002,B0001,etc', ',')
but how do I pass my field value, e.g.
Select * From MY_FUNCTION([Select kPEAccountCode from dbo.dbProfile], ',') doesn't work
TIA
Regards
December 11, 2009 at 11:00 am
Read this article by Jeff Moden. About half way through the article I think you will find what you need.
December 12, 2009 at 2:24 pm
Paul-755326 (12/12/2009)
thank you for the information - very interesting. I solved my problem by using the following TSQL as source data in my DTS...DECLARE @STR varchar(8000)
SELECT @STR=kPEAccountCode from dbo.dbProfile
Select * From fnStringToTable(@Str, ';')
Would you mind showing us the code for the function, please?
--Jeff Moden
Change is inevitable... Change for the better is not.
December 13, 2009 at 2:25 am
Jeff Moden (12/12/2009)
Paul-755326 (12/12/2009)
thank you for the information - very interesting. I solved my problem by using the following TSQL as source data in my DTS...DECLARE @STR varchar(8000)
SELECT @STR=kPEAccountCode from dbo.dbProfile
Select * From fnStringToTable(@Str, ';')
Would you mind showing us the code for the function, please?
Hi Jeff.
It is this...
CREATE FUNCTION [dbo].[fnStringToTable]
(
@string VARCHAR(100),
@delimiter CHAR(1)
)
RETURNS @output TABLE(
data VARCHAR(256)
)
BEGIN
DECLARE @start INT, @end INT
SELECT @start = 1, @end = CHARINDEX(@delimiter, @string)
WHILE @start < LEN(@string) + 1 BEGIN
IF @end = 0
SET @end = LEN(@string) + 1
INSERT INTO @output (data)
VALUES(SUBSTRING(@string, @start, @end - @start))
SET @start = @end + 1
SET @end = CHARINDEX(@delimiter, @string, @start)
END
RETURN
END
It is a function I found somewhere on the net
December 13, 2009 at 6:24 am
Ouch! You can do much better than a WHILE loop. I strongly advise to read the article that bitbucket pointed out to you (http://www.sqlservercentral.com/articles/T-SQL/63003/) which as it happens is by Jeff Moden. It has much superior ways to do this.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 13, 2009 at 6:42 am
RBarryYoung (12/13/2009)
Ouch! You can do much better than a WHILE loop. I strongly advise to read the article that bitbucket pointed out to you (http://www.sqlservercentral.com/articles/T-SQL/63003/) which as it happens is by Jeff Moden. It has much superior ways to do this.
I shall do, but my source table only ever has one record and it is run via DTS, so it is fine for that use.
Regards
December 13, 2009 at 9:30 am
Paul-755326 (12/13/2009)
RBarryYoung (12/13/2009)
Ouch! You can do much better than a WHILE loop. I strongly advise to read the article that bitbucket pointed out to you (http://www.sqlservercentral.com/articles/T-SQL/63003/) which as it happens is by Jeff Moden. It has much superior ways to do this.I shall do, but my source table only ever has one record and it is run via DTS, so it is fine for that use.
Regards
I absolutely agree. It's fine for "that" use.
The problem is that if someone else ends up with a similar requirement in a multi-row environment (heh... I'm a poet and don't know it) and they find that code, they'll end up using it likely because of scheduling pressures. If they do, they'll ultimately end up with a performance nightmare.
"Good enough" usually isn't.
You're also missing the opportunity to learn something new about performance code.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 14, 2009 at 2:22 am
Jeff Moden (12/13/2009)
Paul-755326 (12/13/2009)
RBarryYoung (12/13/2009)
Ouch! You can do much better than a WHILE loop. I strongly advise to read the article that bitbucket pointed out to you (http://www.sqlservercentral.com/articles/T-SQL/63003/) which as it happens is by Jeff Moden. It has much superior ways to do this.I shall do, but my source table only ever has one record and it is run via DTS, so it is fine for that use.
Regards
I absolutely agree. It's fine for "that" use.
The problem is that if someone else ends up with a similar requirement in a multi-row environment (heh... I'm a poet and don't know it) and they find that code, they'll end up using it likely because of scheduling pressures. If they do, they'll ultimately end up with a performance nightmare.
"Good enough" usually isn't.
You're also missing the opportunity to learn something new about performance code.
Hello Jeff - I very much appreciate your point and your guidance, however my original question wasn't about how to perform the split itself, but how to call functions generally. This is why I didn't initially post the code that I had found, as I didn't feel it was relevant in itself to my question and I just left it as background information.
I tend to make very great use of online forums and have done for years. I am a veteran of other technologies and have provided help to hundrends of other people over the years in the relevant forums to that technology. I am currently a SQL novice so am not able to contribute much myself to SQL forums. I have recently posted another question on this forum which went unanswered. Luckily I was able to solve the problem myself and I left the solution to my own question as a response in case other had the same problem.
The difficulty I have been having in trying to learn these techniques was in being able to practicably apply solutions and examples provided by others. I unfortunately don't have the luxury of working for a company with large training budgets 🙁 I also have a go-live deadline looming which is not leaving me much time to investigate thins properly. Improvements and efficiency gains will come later.
I am very likely to perform more of this sort of split in the future (much of my source data comes from Domino which can use multi-value fields), and as and when I do I will investigate the more efficient methods that you have pointed out.
Thank you once again for your assistance. I hope to gain from it again in the future and eventually contribute myself.
Kind regards
Paul
December 14, 2009 at 10:27 pm
Paul-755326 (12/14/2009)
Improvements and efficiency gains will come later.
Heh... no they won't... no one will give anyone the time until they actually become a problem, at which point, it will be too late. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
December 16, 2009 at 11:13 am
Jeff Moden (12/14/2009)
Paul-755326 (12/14/2009)
Improvements and efficiency gains will come later.Heh... no they won't... no one will give anyone the time until they actually become a problem, at which point, it will be too late. 😉
How true!!! I have yet to find the time to go back.
"When in danger or in doubt. Run in circles, scream and shout!" TANSTAAFL
"Robert A. Heinlein"
December 16, 2009 at 12:21 pm
In my standard comment headers I tend to add a specific phrase (like 'Not yet Optimized' or 'Needs Rewrite') when I'm not happy with how something works, or the method it uses but don't have the time to spend researching/optimizing a better way at the moment. This most commonly happens when the scope of a bigger SP/report completely changes half a dozen times after the fact and I have to hack in so many different modifications that starting over would probably yield a much cleaner result.
The *idea* is that I can do a search for the word 'optimization' or 'rewrite' when I have free time and find all the queries that I flagged to be updated later. The reality is that for the most part it never happens and the comments just serve as a weak disclaimer for the person who finds it behind me so they don't think I was completely clueless.
Some of it is unavoidable, but the more time you spend learning the better ways to do something, the more you will naturally go to those first and the optimizations required will be much smaller. (IE. I might not be happy with a piece of code now because I feel that it uses a cast or convert that isn't necessary, or the datatypes aren't appropriate, where as a few years ago I wouldn't have been happy with it because it used nested cursors.)
As far as your company not paying for training... that's not all on them. There is a ton of free training or self training available for SQL server. Between this site, articles, sites like SQLShare, blogs, books, SQL Server user group meetings, SQL Saturdays etc. etc. out there that it's really not much of an excuse.
December 16, 2009 at 12:32 pm
Also regarding the company providing training; it's nice when they do, but in the end it is up to us to keep ourselves current and relavent in our rapidly changing technological world. If we wait for our companies to do that, we'll never be current.
December 16, 2009 at 12:39 pm
Lynn Pettis (12/16/2009)
Also regarding the company providing training; it's nice when they do, but in the end it is up to us to keep ourselves current and relavent in our rapidly changing technological world. If we wait for our companies to do that, we'll never be current.
Pretty well nails it here:
http://www.dilbert.com/2009-12-12/
and
http://www.dilbert.com/strips/comic/2009-11-16/
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 16, 2009 at 12:54 pm
GSquared (12/16/2009)
Lynn Pettis (12/16/2009)
Also regarding the company providing training; it's nice when they do, but in the end it is up to us to keep ourselves current and relavent in our rapidly changing technological world. If we wait for our companies to do that, we'll never be current.Pretty well nails it here:
http://www.dilbert.com/2009-12-12/
and
Perfect. I'd seen the first, but not the second one. 😛
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply