how to extract the digit part from the string

I have a column that has a string in it as follow

Data
black83736
white87
whi开发者_Go百科te893
pink82928
pink1
black27
...
...

I want to just get the digits out of that column.

Edit

I would prefer mysql solution

---

Try this mysql Solution : (updated)

SELECT replace(reverse(FORMAT(reverse(COLUMN_NAME), 0)), ',', '') as number from TABLE_NAME;

this is for your particular case, where you want the numbers in the end.

so first we

Reverse, so numbers come to beginning

format, so alphabets are removed, format is used for formatting a number with specified decimal places, here we specify it to 0.

reverse again -> to obtain the original number

replace ',' to '' -> to remove the effect of formatting of second step.

**

(UPDATE)

** the above solution doesnt work for numbers ending in 0's (thanks to karolis for telling)

so for that

SELECT replace(replace(reverse(FORMAT(reverse(concat(numbers, '9099')), 0)), ',', ''), '9099', '') as number from test1;

Here we are appending '9099' to the end of the number, and then removing 9099 in the end.

9099 ? just a number which would be highly in-probable to occur in your columns

---

$result = preg_replace('~[^0-9]*~', '', $string);

---

Yes, it's very inelegant, but currently the only reliable MySQL only solution that I know:

select replace(column_name,
    replace(replace(replace(replace(replace(
    replace(replace(replace(replace(replace(
    column_name, 
    0, ''), 1, ''), 2, ''), 3, ''), 4, ''), 
    5, ''), 6, ''), 7, ''), 8, ''), 9, ''), '')

---

filter_var('stri99ng', FILTER_SANITIZE_NUMBER_INT);

I would prefer mysql solution

Well, you shouldn't unless you have powerful processor or this query does not return many results. MySQL isn't generally good for any string manipulation operations.