Home / Questions / 44699
Accepted
Hoose
Asked: 2020-01-19 07:03:19 +0800 CST 2020-01-19 07:03:19 +0800 CST

How does the IN function work?

  • 772

I have the following scenario, in which I need to enter all of them UBI_ID_UBICACIONinto a textarea, for each enter the script recognizes that it is a location and adds what is necessary to place it inside the IN function, the bad thing is that it can be escaped (by the user ) a character that is not a number.

However, if I use the IN function, it gives me the following "error":

SELECT
    STA_INV_ID,
    STA_INV_NOMBRE,
    UBI_ID,
    CONCAT(
        UBI_ID_UBICACION,
        ' ',
        UBI_NOMBRE
    ) AS UBICACION,
    UBI_ID_UBICACION
FROM
    cat_status_inventario
INNER JOIN cat_per_ubicaciones
WHERE
    STA_INV_ID = 1
AND UBI_ID_UBICACION IN ('6333dsds')
AND UBI_ID_UBICACION <> '0'
GROUP BY
    STA_INV_ID,
    STA_INV_NOMBRE,
    UBI_ID

This query brings me the following record, which should NOT do it since in the IN I look for '6333dsds', that UBI_ID_UBICACION does not exist.

-----------------------------------
    UBICACION   |  UBI_ID_UBICACION
-----------------------------------
 6333 BBB-1-6-2 |       6333

The curious thing is that, if I put it in the following way IN ('dsds6333'), it returns NULL, which is ideal, since that UBI_ID_UBICACION does not exist in the table.

-----------------------------------
    UBICACION   |  UBI_ID_UBICACION
-----------------------------------
      NULL      |      NULL

The correct location would be just IN ('6333')to bring me the record. [![enter image description here][3]][3]

As a fact, all locations are just numbers.

Do you know what could be the problem and how could I solve it?

mysql

2 Answers

  1. Greetings, In your case the problem is that you try to evaluate an alphanumeric expression against a numeric one, of course it will not return any result. In your case, what I would do is create a function similar to the one you will find below, which extracts integers from an expression:

        DROP FUNCTION IF EXISTS extractNumber;
DELIMITER //
CREATE FUNCTION extractNumber (cadena1 VARCHAR(255)) 
RETURNS INT(11)
DETERMINISTIC
READS SQL DATA
    BEGIN
    DECLARE posicion, resultado, longitud INT(11) DEFAULT 0;
    DECLARE cadena2 VARCHAR(255);
    SET longitud = LENGTH(cadena1);
    SET resultado = CONVERT(cadena1, SIGNED);

    IF resultado = 0 THEN
        IF cadena1 REGEXP('[0-9]') THEN
            SET posicion = 2;
            checkString:WHILE posicion <= longitud DO
                SET cadena2 = SUBSTR(cadena1 FROM posicion);
                IF CONVERT(cadena2, SIGNED) != 0 THEN
                    SET resultado = CONVERT(cadena2, SIGNED);
                    LEAVE checkString;
                END IF;
                SET posicion = posicion + 1;
            END WHILE;
        END IF;
    END IF;
    RETURN resultado;
END //
DELIMITER ;

    I would then modify your query as follows:

    SELECT
    STA_INV_ID,
    STA_INV_NOMBRE,
    UBI_ID,
    CONCAT(
        UBI_ID_UBICACION,
        ' ',
        UBI_NOMBRE
    ) AS UBICACION,
    UBI_ID_UBICACION
FROM
    cat_status_inventario
INNER JOIN cat_per_ubicaciones
WHERE
    STA_INV_ID = 1
AND UBI_ID_UBICACION = extractNumber ('6333dsds')
AND UBI_ID_UBICACION <> '0'
GROUP BY
    STA_INV_ID,
    STA_INV_NOMBRE,
    UBI_ID

    I changed the IN to = since if you evaluate a single expression it is not recommended to use IN, since it decreases the efficiency of the query.

    I hope my contribution will help you. Here is a link that may be useful to you. Functions to extract numbers See you!

    • 0
  2. Best Answer

    The solution to my problem was to use the CAST function and within it the UBI_ID_UBICACION field 'convert' it to CHAR so that when evaluating, it takes it as a string and not as the data type it has by default (INT).

    SELECT
    STA_INV_ID,
    STA_INV_NOMBRE,
    UBI_ID,
    CONCAT(
        UBI_ID_UBICACION,
        ' ',
        UBI_NOMBRE
    ) AS UBI_NOMBRE
FROM
    cat_status_inventario
INNER JOIN cat_per_ubicaciones
WHERE
    STA_INV_ID = 1
AND CAST(UBI_ID_UBICACION AS CHAR) IN ('6332xsds')
AND UBI_ID_UBICACION <> '0'
GROUP BY
    STA_INV_ID,
    STA_INV_NOMBRE,
    UBI_ID
    • 0