Come selezionare solo dati numerici da mysql?

Mysql ha una funzione come is_num () che può permettermi di determinare se i dati sono numerici o no?

Potresti voler creare una funzione definita dall’utente che corrisponda al valore rispetto a un’espressione regolare:

CREATE FUNCTION IsNumeric (sIn varchar(1024)) RETURNS tinyint RETURN sIn REGEXP '^(-|\\+){0,1}([0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+|[0-9]+)$'; 

Fonte: Forum MySQL :: Microsoft SQL Server :: Clausola IsNumeric () in MySQL?


Test di verità:

 mysql> SELECT ISNUMERIC('1'); +----------------+ | ISNUMERIC('1') | +----------------+ | 1 | +----------------+ 1 row in set (0.01 sec) mysql> SELECT ISNUMERIC(25); +---------------+ | ISNUMERIC(25) | +---------------+ | 1 | +---------------+ 1 row in set (0.00 sec) mysql> SELECT ISNUMERIC('-100'); +-----------------+ | ISNUMERIC(-100) | +-----------------+ | 1 | +-----------------+ 1 row in set (0.03 sec) mysql> SELECT ISNUMERIC('1.5'); +------------------+ | ISNUMERIC('1.5') | +------------------+ | 1 | +------------------+ 1 row in set (0.00 sec) mysql> SELECT ISNUMERIC('-1.5'); +-------------------+ | ISNUMERIC('-1.5') | +-------------------+ | 1 | +-------------------+ 1 row in set (0.02 sec) 

Test di Falsy:

 mysql> SELECT ISNUMERIC('a'); +----------------+ | ISNUMERIC('a') | +----------------+ | 0 | +----------------+ 1 row in set (0.02 sec) mysql> SELECT ISNUMERIC('a1'); +-----------------+ | ISNUMERIC('a1') | +-----------------+ | 0 | +-----------------+ 1 row in set (0.00 sec) mysql> SELECT ISNUMERIC('10a'); +------------------+ | ISNUMERIC('10a') | +------------------+ | 0 | +------------------+ 1 row in set (0.00 sec) mysql> SELECT ISNUMERIC('0.a'); +------------------+ | ISNUMERIC('0.a') | +------------------+ | 0 | +------------------+ 1 row in set (0.00 sec) mysql> SELECT ISNUMERIC('a.0'); +------------------+ | ISNUMERIC('a.0') | +------------------+ | 0 | +------------------+ 1 row in set (0.00 sec) 

Nel mio caso ho solo bisogno di verificare se i dati fossero> = 0 e non char, forse questo è utile anche per te:

 mysql> select '1' REGEXP '^[0-9]+$' as result; +--------+ | result | +--------+ | 1 | +--------+ 1 row in set (0.16 sec) mysql> select '1a' REGEXP '^[0-9]+$' as result; +--------+ | result | +--------+ | 0 | +--------+ 

Se si desidera selezionare solo i caratteri numerici della stringa, provare a utilizzare questo:

 CREATE FUNCTION IsNumeric (val varchar(255)) RETURNS tinyint RETURN val REGEXP '^(-|\\+){0,1}([0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+|[0-9]+)$'; CREATE FUNCTION NumericOnly (val VARCHAR(255)) RETURNS VARCHAR(255) BEGIN DECLARE idx INT DEFAULT 0; IF ISNULL(val) THEN RETURN NULL; END IF; IF LENGTH(val) = 0 THEN RETURN ""; END IF; SET idx = LENGTH(val); WHILE idx > 0 DO IF IsNumeric(SUBSTRING(val,idx,1)) = 0 THEN SET val = REPLACE(val,SUBSTRING(val,idx,1),""); SET idx = LENGTH(val)+1; END IF; SET idx = idx - 1; END WHILE; RETURN val; END; 

Usalo chiamando la funzione NumericOnly in questo modo:

 select NumericOnly('1&2') as result; 

Restituisce: “12”

 select NumericOnly('abc987') as result; 

Restituisce: “987”

(A proposito, ho scritto un post su questo qui )

È sufficiente convertire i dati in modo esplicito in un numero utilizzando DECIMAL o SIGNED / UNSIGNED e utilizzare STRCMP per confrontare il valore dei dati originali. Se STRCMP restituisce 0 (uguaglianza), allora sai di avere un numero. Altrimenti, non lo è.

Ecco un esempio che presuppone che non vi siano posizioni decimali nei dati quando sono numerici, quindi è ansible eseguire il cast di DECIMAL.

Questa query seleziona tutte le righe che contengono dati numerici nel campo is_it_a_number.

 SELECT * FROM my_table WHERE STRCMP(CAST(my_table.is_it_a_number AS DECIMAL(10,0)), my_table.is_it_a_number) = 0; 

La syntax semplice per IsNumeric da SQL a MYSQL è

sql:

 SELECT isNumeric(col_name) FROM Table where isNumeric(col_name) <> 0 

mysql

 select concat('',col_name * 1) from table where concat('',col_name * 1) <> 0; 

Mille volte più veloce.

 DROP FUNCTION IF EXISTS NumericOnly; DELIMITER $$ CREATE FUNCTION NumericOnly(val VARCHAR(25)) RETURNS VARCHAR(25) BEGIN DECLARE idx INT DEFAULT 0; IF ISNULL(val) THEN RETURN NULL; END IF; IF LENGTH(val) = 0 THEN RETURN ""; END IF; SET idx = LENGTH(val); WHILE idx > 0 DO IF (SUBSTRING(val,idx,1) in ('0','1','2','3','4','5','6','7','8','9')) = 0 THEN SET val = CONCAT(SUBSTRING(val, 1, idx-1), SUBSTRING(val FROM idx + 1)); END IF; SET idx = idx - 1; END WHILE; RETURN val; END; $$ DELIMITER ;