how to split a delimited field value into rows

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

Transact-SQL (2000)

how to split a delimited field value into rows

Author

Topic

PaulDavies
Starting Member

4 Posts

Posted - 2009-12-11 : 12:41:24

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-12-11 : 13:06:41

take it in a variable and then use

DECLARE @Str varchar(8000)

SELECT @Str=kPEAccountCode from dbo.dbProfile

and use it like

Select * From MY_FUNCTION(@Str, ',')

PaulDavies
Starting Member

4 Posts

Posted - 2009-12-12 : 09:57:48

quote:
Originally posted by visakh16

take it in a variable and then use

DECLARE @Str varchar(8000)

SELECT @Str=kPEAccountCode from dbo.dbProfile

and use it like

Select * From MY_FUNCTION(@Str, ',')

Fantastic - thank you - My TSQL to date is very much limited to what can be done in a view. This will help me progress. :D

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources