Parse column value based on delimeters

Question

Here is a sample of my data:

ABC*12345ABC
BCD*234()
CDE*3456789(&(&
DEF*4567A*B*C

Using SQL Server 2008 or SSIS, I need to parse this data and return the following result:

12345
234
3456789
4567

As you can see, the asterisk (*) is my first delimiter. The second "delimiter" (I use this term loosely) is when the sequence of numbers STOP.

So, basically, just grab the sequence of numbers after the asterisk...

How can I accomplish this?

EDIT:

I made a mistake in my original post. An example of another possible value would be:

XWZ*A12345%$%

In this case, I would like to return the following:

A12345

The value can START with an alpha character, but it will always END with a number. So, grab everything after the asterisk, but stop at the last number in the sequence.

Any help with this will be greatly appreciated!

Answer 1

You could do this with a little patindex and charindex trickery, like:

; with YourTable(col1) as 
        (
        select 'ABC*12345ABC'
        union all select 'BCD*234()'
        union all select 'CDE*3456789(&(&'
        union all select 'DEF*4567A*B*C'
        union all select 'XWZ*A12345%$%'
        )
select  left(AfterStar, len(Leader) + PATINDEX('%[^0-9]%', AfterLeader) - 1)
from    (
        select  RIGHT(AfterStar, len(AfterStar) - PATINDEX('%[0-9]%', AfterStar) + 1) 
                    as AfterLeader
        ,       LEFT(AfterStar, PATINDEX('%[0-9]%', AfterStar) - 1) as Leader
        ,       AfterStar
        from    (
                select  RIGHT(col1, len(col1) - CHARINDEX('*', col1)) as AfterStar
                from    YourTable
                ) as Sub1
        ) as Sub2

This prints:

12345
234
3456789
4567
A12345

Answer 2

If you ignore that this is in SQL then the first thing that comes to mind is Regex:

^.*\*(.*[0-9])[^0-9]*$

The capture group there should get what you want. I don't know if SQL has a regex function.