1

In MySQL DB 

Colum1

Drill 14"
Drill 15"
Drill 10" 
Drill 11"
Drill 5"

I want to get the numerical values and sort it like so

Colum1

5
10
11
14
15

Note that i don't want to use declare because it does not accepted by the Jasper - so if there is simple SQL that can do it should be fine

Improve this question

2 Answers 2

Reset to default
2

You can use SUBSTRING_INDEX

ORDER BY SUBSTRING_INDEX(Colum1,' ', -1)+0 ASC

Demonstration:

SET @str := 'Drill 11';

SELECT SUBSTRING_INDEX(@str,' ',-1)+0 AS number;

Output:

number 
11

Demo here:

SQLFiddle

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

5 Comments

I saw the fiddle it still include the words Drill -- i want to remove them and get only the numbers
Check this fiddle. Thanks a million @Tim
Alternatively you can use CAST function to achieve this. Check this fiddle too. @rookie_coder
By the way is the sql treat 12 1/4 inch as numbers or string? i meant the 1/4
You cannot make it work for this kind of numeric strings. @rookie_coder
1

Try This

        SELECT * 
        FROM Table_Name 
        WHERE Colum1 REGEXP '^[0-9]+$';
Improve this answer

1 Comment

Brilliant this is also working...thanks but only specifically for the one that has numbers inside maybe because it is put in where?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.