CODEWITHSUNDEEP
phpmysql
Asim Zaidi
Asim Zaidi

Reputation: 28284

how to extract the digit part from the string

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

Data
black83736
white87
white893
pink82928
pink1
black27
...
...

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

Edit

I would prefer mysql solution

Upvotes: 3

Views: 166

Answers (4)

Karolis
Karolis

Reputation: 9562

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, ''), '')

Upvotes: 1

Pheonix
Pheonix

Reputation: 6052

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

Upvotes: 4

Gajus
Gajus

Reputation: 73808

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.

Upvotes: 0

genesis
genesis

Reputation: 50976

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

Upvotes: 3

Related Questions