Comment extraire une valeur numérique d'une chaîne dans une requête MySQL?

J'ai un tableau avec deux colonnes: le prix (int) et price_display (varchar).

prix est le prix numérique, par exemple "9990"

price_display est la représentation visuelle, par exemple, "$9.99" ou "9.99 Fr"

J'ai été en mesure de confirmer les deux colonnes correspondent via regexp:

price_display pas regexp
format(price/1000, 2)

Mais dans le cas d'une incompatibilité, je veux extraire la valeur de la price_display colonne et dans la colonne des prix, le tout dans le contexte d'une instruction de mise à jour. Je n'ai pas été en mesure de comprendre comment.

Grâce.

source d'informationauteur Teflon Ted

Cette fonction fait le travail de renvoyant uniquement des chiffres de 0 à 9 à partir de la chaîne, qui fait le travail très bien de résoudre votre problème, indépendamment de ce que les préfixes ou postfixes vous avez.

http://www.artfulsoftware.com/infotree/queries.php?&bw=1280#815

Copié ici pour référence:

SET GLOBAL log_bin_trust_function_creators=1;
DROP FUNCTION IF EXISTS digits;
DELIMITER |
CREATE FUNCTION digits( str CHAR(32) ) RETURNS CHAR(32)
BEGIN
  DECLARE i, len SMALLINT DEFAULT 1;
  DECLARE ret CHAR(32) DEFAULT '';
  DECLARE c CHAR(1);

  IF str IS NULL
  THEN 
    RETURN "";
  END IF;

  SET len = CHAR_LENGTH( str );
  REPEAT
    BEGIN
      SET c = MID( str, i, 1 );
      IF c BETWEEN '0' AND '9' THEN 
        SET ret=CONCAT(ret,c);
      END IF;
      SET i = i + 1;
    END;
  UNTIL i > len END REPEAT;
  RETURN ret;
END |
DELIMITER ;

SELECT digits('$10.00Fr'); 
#returns 1000

4

Une approche serait d'utiliser REPLACE() fonction:
```
UPDATE my_table
SET    price = replace(replace(replace(price_display,'Fr',''),'$',''),'.','')
WHERE  price_display not regexp format(price/1000, 2);
```
Cela fonctionne pour les exemples de données que vous avez donné:
```
'$9.99'
'9.99Fr'
```
La fois le résultat de 999 dans mon test. Avec une mise à jour comme cela, il est important d'être sûr de sauvegarder la base de données tout d'abord, et être au courant de la présentation des articles. Vous pouvez voir tous les "méchants" en faisant cette requête:
```
SELECT   DISTINCT price_display
FROM     my_table
WHERE    price_display not regexp format(price/1000, 2)
ORDER BY price_display;
```
4

Pour moi le CASTING du domaine a fait le tour:

CAST( price AS UNSIGNED ) //Pour l'entier positif

CAST( price AS SIGNED ) //Pour le négatif et le positif entier

IF(CAST(price AS UNSIGNED)=0,REVERSE(CAST(REVERSE(price) AS UNSIGNED)),CAST(price AS UNSIGNED)) //Fix lorsque le cours commence par quelque chose d'autre, puis un chiffre

Pour plus de détails, voir:

https://dev.mysql.com/doc/refman/5.0/en/cast-functions.html

J'ai créer une procédure qui détectent le premier nombre dans une chaîne de caractères, et le renvoyer, si pas 0.

    DROP FUNCTION IF EXISTS extractNumber;
    DELIMITER //
    CREATE FUNCTION extractNumber (string1 VARCHAR(255)) RETURNS INT(11) 
        BEGIN
        DECLARE position, result, longitude INT(11) DEFAULT 0;
        DECLARE string2 VARCHAR(255);
        SET longitude = LENGTH(string1);
        SET result = CONVERT(string1, SIGNED);
        IF result = 0 THEN
            IF string1 REGEXP('[0-9]') THEN
                SET position = 2;
                checkString:WHILE position <= longitude DO
                    SET string2 = SUBSTR(string1 FROM position);
                    IF CONVERT(string2, SIGNED) != 0 THEN
                        SET result = CONVERT(string2, SIGNED);
                        LEAVE checkString;
                    END IF;
                    SET position = position + 1;
                END WHILE;
           END IF;
        END IF;
        RETURN result;
    END //
    DELIMITER ;

Retour du dernier numéro de la chaîne:

CREATE FUNCTION getLastNumber(str VARCHAR(255)) RETURNS INT(11)
DELIMETER //
BEGIN
    DECLARE last_number, str_length, position INT(11) DEFAULT 0;
    DECLARE temp_char VARCHAR(1);
    DECLARE temp_char_before VARCHAR(1);


IF str IS NULL THEN
    RETURN -1;
END IF;

SET str_length = LENGTH(str);

WHILE position <= str_length DO
    SET temp_char = MID(str, position, 1);

    IF position > 0 THEN
        SET temp_char_before = MID(str, position - 1, 1);
    END IF;

    IF temp_char BETWEEN '0' AND '9' THEN
        SET last_number = last_number * 10 + temp_char;
    END IF;
    IF (temp_char_before NOT BETWEEN '0' AND '9') AND 
           (temp_char BETWEEN '0' AND '9') THEN                 
        SET last_number = temp_char;
    END IF;

    SET position = position + 1;
END WHILE;

RETURN last_number;
END//
DELIMETER;

Ensuite appeler cette fonction:

sélectionnez getLastNumber("ssss111www222w");
imprimer 222

sélectionnez getLastNumber("ssss111www222www3332");
imprimer 3332

0

C'est un "codage de l'horreur", base de données relationnelle schémas doivent PAS être écrit comme ceci!

Votre devoir écrire complexes et inutiles code pour valider les données.

Essayer quelque chose comme cela:
```
SELECT CONCAT('$',(price/1000)) AS Price FROM ...
```
En outre, vous pouvez utiliser un floatdouble ou real au lieu d'un entier.

Si vous avez besoin de stocker des données de devise, vous pourriez envisager d'ajouter un champ de devise ou de l'utilisation des systèmes de fonctions locales pour l'afficher dans le format correct.

Vous devez vous connecter pour publier un commentaire.