Was looking at this and thinking there has to be a set-based way to acheive the same thing.

This is what I've come up with so far. Might be useful to read Jeff Modens article on Tally tables first (link in my sig below) if you're not already familiar with it.

I've omitted a few things on purpose so as not to obscure the main details of the query, like casting to decimal and checking for invalid strings with ISNUMERIC.

DECLARE @t TABLE(test varchar(8000))

INSERT @t

SELECT ' 1 iop 2?,.3e[]-4-@135.678;0--0;0...0'

SELECT

N,

number = SUBSTRING(test,N,PATINDEX('%[-.0123456789][^.0123456789]%',SUBSTRING(test+',',N,LEN(test)-N+2)))

FROM

@t, Tally

WHERE

N <= LEN(test) -- Restrict rows to the number of cahracters in the string

AND

CHARINDEX(SUBSTRING(test,N,1),'-.0123456789') > 0 -- Only rows starting with a valid numeric start character

AND

(N=0 OR CHARINDEX(SUBSTRING(test,N-1,1),'-.0123456789') = 0) -- either at the start of the string or following an invalid character.

This is basically an extrapolation of Jeffs string-splitting method.

In some basic tests I've done it's outperformed the looping method by a factor of 7 or 8.

I'm sure there are even faster methods and I've no doubt some of the more experienced posters on this site will be along to show us some CLR or XML method that's way beyond me 🙂