function to extract delimited data

[rcamarda]

I'd need to have a function that allows me to extract 'fields' from
within the string
I.E. (kinda pseudo code)
declare @foo as varchar(100)
set @foo = "Robert*Camarda *123 Main Street"
select EXTRACT(@foo, '*', 2) ; -- would return 'Camarda'
select EXTRACT(@foo, '*', 3) ;-- returns '123 Main Street'
select EXTRACT(@foo, '*', 0) ;-- would return entire string
select EXTRACT(@foo,'* ' , 9) ;-- would return null

Extract( string, text delimiter, occurance)
Anyone have something like this as a user defined function in SQL?
TIA
Rob

[Erland Sommarskog]

Re: function to extract delimited data

rcamarda (robert.a.camar da@gmail.com) writes:

I'd need to have a function that allows me to extract 'fields' from
within the string
I.E. (kinda pseudo code)
declare @foo as varchar(100)
set @foo = "Robert*Camarda *123 Main Street"
select EXTRACT(@foo, '*', 2) ; -- would return 'Camarda'
select EXTRACT(@foo, '*', 3) ;-- returns '123 Main Street'
select EXTRACT(@foo, '*', 0) ;-- would return entire string
select EXTRACT(@foo,'* ' , 9) ;-- would return null
>
Extract( string, text delimiter, occurance)
Anyone have something like this as a user defined function in SQL?

A whole bunch of them here: http://www.sommarskog.se/arrays-in-sql.html.
OK, so none of them returns a specific field from the string,
but several returns a list position to filter on.




--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx