Extract Number Into New Column

[The Little Guy]

I want to extract a number from a string and place it into a new column.

 

The string looks like this:

Bonus AdPacs from Product purchase #1234567890

 

I was thinking that REGEXP would work, but that returns 1 or 0, not the number. Using mysql how can I get that number? I don't want to use PHP for this.

 

Any ideas would be helpful!

 

Thanks!

[xyph]

Ooops, redoing. One sec.

 

SET @str='Bonus AdPacs from Product purchase #1234567890';

SELECT SUBSTRING( @str, LOCATE('#', @str)+1 )

[PFMaBiSmAd]

Do all the numbers start with the # and will there only be one # in each string?

[The Little Guy]

Do all the numbers start with the # and will there only be one # in each string?

 

yes and yes.

 

I decided to do substring(), which worked in this case.