CODEWITHSUNDEEP
mysqlsqlstring
genie
genie

Reputation: 305

Find the last 5 digits even when there is a character and convert them to integer

I have to always get the last 5 digits from a string and need to convert it to int. But I have situations where the 5th digit from the end is a character. If I have the a character then I want to just get the number.

Sample data:

Input                  Expected Output
978568-16258            16258 
ERGF99252697            52697
SP-988824-189241        89241
SP-456790-568723        68723
SP-456790-568           568

I'have tried some thing like this:

select CAST((RIGHT(RTRIM(col_1),5)) AS UNSIGNED INT) as test from table_A;

For few of the reults its ok but when it sees characters then it displays a random number.

How can I resolve this issue?

Upvotes: 1

Views: 205

Answers (2)

ysth
ysth

Reputation: 98388

For older versions of MySQL/MariaDB, you can do:

select cast(right(col_1,
    (col_1 regexp "[0-9]{1}$") +
    (col_1 regexp "[0-9]{2}$") +
    (col_1 regexp "[0-9]{3}$") +
    (col_1 regexp "[0-9]{4}$") +
    (col_1 regexp "[0-9]{5}$")
) as unsigned) from table_A;

Upvotes: 0

GMB
GMB

Reputation: 222432

If you are running MySQL 8.0 / MariaDB 10.0.5, you can use regexp_substr() for this. This should be as simple as:

select cast(regexp_substr(col_1, '[0-9]{1,5}$') as unsigned) from table_A

Regexp '[0-9]{1,5}$' means: as many digits as possible (maximum 5) at the end of the string.

Demo on DB Fiddle:

col_1            | col_1_new
:--------------- | :--------
978568-16258     | 16258    
ERGF99252697     | 52697    
SP-988824-189241 | 89241    
SP-456790-568723 | 68723    
SP-456790-568    | 568

Upvotes: 1

Related Questions