Clever Geek Handbook

Select odd numbers and contains no special characters

This is the table WWE

:

    |**Col A**  | **Col B** |
   -+-----------+-----------+-    
    | Hello     | 9%15A     |    
    | World     | 2*10      |    
    | Cat       | 12        |    
    | Dog       | 13        |

Col B

Varchar (100)

How do I select all rows consisting of ONLY ODD NUMBERS and not containing "%" or "*" or alphabets?

I know how to get ODD NUMBERS: colB % 2 <> 0

But I don't know how to exclude special characters and calculate the number as NUMBER since they are stored in VARCHAR

For example, in this table, the output would be

| Dog | 13 |
+3


source to share


3 answers


You can check both conditions with one regex:

SELECT * FROM WWE WHERE colB REGEXP '^[[:digit:]]*[13579]$';

What can be read as:

Begins a digit, followed by any number of digits and ends with any of these characters (1,3,5,7)

For example data



    |**Col A**  | **Col B** |
   -+-----------+-----------+-    
    | Hello     | 9%15A     |    
    | World     | 2*10      |    
    | Cat       | 12        |    
    | Dog       | 13        |

Returns

    |**Col A**  | **Col B** |
   -+-----------+-----------+-      
    | Dog       | 13        |

Updated to account for individual numbers, thanks to @Thorsten Kettner

+3


source


USE REGEXP

if you only want a numeric result:



SELECT * FROM WWE WHERE colB REGEXP '[0-9]' AND colB mod 2 = 1
+1


source


Or use the ISNUMERIC function

SELECT * FROM WWE WHERE ISNUMERIC(colB) = 1 AND colB % 2 <> 0
+1


source






More articles:


All Articles