Here is a way that I recently found using PATINDEX useful:

http://codeslammer.wordpress.com/2008/09/02/sql-server-string-manipulation-removing-non-numeric-characters/

I have a column that contains an ID number, but there may be text before and/or after it. The text is often similar, but it can not be guaranteed that there will be text or not. However there should only be one number and this is the number I am trying to extract.

For example, if you had a column with values such as:

"Serial Number: 3078203984-02"

"SN#472038572289"

"Serial No. 02-28349-2074"

and you just wanted to strip out the unique serial numbers, this is one option to do it.