How to check integer value in a field using mysql

Question

I am having a field in a table with type varchar and their value looks similar to

In this i need to get only values that ends with hypen followed by integer ex ("-94581" , "-93945") ..

Is there any way in mysql to get that

U can use dev.mysql.com/doc/refman/5.1/en/… to cut X chars from end of string — kskaradzinski
– kskaradzinski, Commented May 22, 2012 at 12:22
@juergen d its not number, he has varchar field after cutting it still will be string, substring_index will not cast it to number — kskaradzinski
– kskaradzinski, Commented May 22, 2012 at 12:27
@skowron-line: yes. And he wants to check for numbers at the end of his string which substring() can't. — juergen d
– juergen d, Commented May 22, 2012 at 12:29

robert · Accepted Answer · 2012-05-22 14:12:57Z

2

Use the RLIKE operator in place of LIKE. This should work:

SELECT * FROM table WHERE foo RLIKE '.*-[[:digit:]]*$'

answered May 22, 2012 at 12:24

robert

34.6k8 gold badges55 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

This one worked for me with slight modification SELECT * FROM table WHERE foo RLIKE '.*-[[:digit:]]{3,}$'

Rahul · Accepted Answer · 2012-05-22 12:41:06Z

0

Probably like this:

select  substr(searchfield,locate('_',searchfield)) as extractedfield from 
mytable where substr(searchfield,locate('_',searchfield)) > 0;

answered May 22, 2012 at 12:28

Rahul

78.1k14 gold badges80 silver badges132 bronze badges