Mouli,

I believe this will solve your problem... no UDF overhead... no need for CROSS-APPLY. Just use the whole thing as another derived table. I suppose it could be a UDF or view, as well.

--===== Create and populate a test table with the data given in the post.

-- This is NOT a part of the solution

CREATE TABLE #YourTable

(

SomeString VARCHAR(20)

)

INSERT INTO #YourTable

SELECT '1230-544' UNION ALL

SELECT '15C5487' UNION ALL

SELECT '132DE78'

--===== This solves the problem.

;WITH

cteSplit AS

(--==== This not only splits out the individual characters, it only splits

-- out the digits from 0 to 9

SELECT SomeString,

ROW_NUMBER() OVER (ORDER BY yt.SomeString) AS CharacterNumber,

SUBSTRING(yt.SomeString,t.N,1) AS Character

FROM #YourTable yt

CROSS JOIN dbo.Tally t

WHERE t.N <= LEN(yt.SomeString)

AND SUBSTRING(yt.SomeString,t.N,1) LIKE '[0-9]'

)--==== This put's it all back together using a very high speed XML method

SELECT t1.SomeString,

CAST((SELECT '' + t2.Character

FROM cteSplit t2

WHERE t1.SomeString = t2.SomeString

ORDER BY t2.CharacterNumber

FOR XML PATH(''))

AS BIGINT) AS NumbersOnly

FROM cteSplit t1

GROUP BY t1.SomeString

If you don't already have a Tally table at hand, now's the time to build this incredibly useful tool. Read the article at the following link not only for how to build one, but to understand how it works, as well.

http://www.sqlservercentral.com/articles/TSQL/62867/

If, for some reason, folks won't let you build one in the database, please post back... there's more than one way to get around that.