MySQL Separate 1 column VARCHAR into 2 columns of type INT. I've Error 1292 Truncated incorret DOUBLE value

Question

I'm trying to separate the information from a VARCHAR column SampleID into two columns of type INT ID and Ampola.

I'm using the CAST and SUBSTRING_INDEX functions.

I tried a 'Update' with 'CAST' and 'SUBSTRING_INDEX', but no success.

I want transform 306465-14 into ID = 306465 and Ampola = 14 or 306466 into ID = 306466 and Ampola = 1, but ignore when SampleID isn't valid.

Below is the table dump and the querys I'm trying to use.

CREATE TABLE `peak` (`SampleID` varchar(255) NOT NULL,`ID` int(11) DEFAULT NULL,`Ampola` int(11) DEFAULT NULL, PRIMARY KEY (`SampleID`));
INSERT INTO `peak` VALUES ('306465-14',NULL,NULL)
                            ,('306465-15',NULL,NULL)
                            ,('306466',NULL,NULL)
                            ,('TESTE',NULL,NULL)
                            ,('TESTE-02',NULL, NULL)
                            ,('off-0101-2020', NULL,NULL);

With SELECT works fine:

SELECT SampleID
    ,CASE 
        WHEN SIGN(SampleID) = 0 THEN NULL
        WHEN CAST(SUBSTRING_INDEX(SampleID, '-', 1) AS UNSIGNED INTEGER) = 0 THEN NULL
        WHEN INSTR(SampleID, '-') = 0 AND SIGN(SampleID) = 0 THEN NULL
        ELSE CAST(SUBSTRING_INDEX(SampleID, '-', 1) AS UNSIGNED INTEGER)
    END AS ID
    ,CASE 
        WHEN INSTR(SampleID, '-') = 0 AND SIGN(SampleID) = 1 THEN 1
        WHEN CAST(SUBSTRING_INDEX(SampleID, '-', 1) AS UNSIGNED INTEGER) = 0 THEN NULL
        ELSE CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(SampleID, '-', 2), '-', -1) AS UNSIGNED INTEGER)
    END AS Ampola
FROM Peak;

Result:

But with UPDATE query

UPDATE Peak
SET ID = 
CASE 
    WHEN SIGN(SampleID) = 0 THEN NULL
    WHEN CAST(SUBSTRING_INDEX(SampleID, '-', 1) AS UNSIGNED INTEGER) = 0 THEN NULL
    WHEN INSTR(SampleID, '-') = 0 AND SIGN(SampleID) = 0 THEN NULL
    ELSE CAST(SUBSTRING_INDEX(SampleID, '-', 1) AS UNSIGNED INTEGER)
END, 
Ampola = 
CASE 
    WHEN INSTR(SampleID, '-') = 0 AND SIGN(SampleID) = 1 THEN 1
    WHEN CAST(SUBSTRING_INDEX(SampleID, '-', 1) AS UNSIGNED INTEGER) = 0 THEN NULL
    ELSE CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(SampleID, '-', 2), '-', -1) AS UNSIGNED INTEGER)
END

Error Message:

Error Code: 1292. Truncated incorrect DOUBLE value: '306465-14'

Thanks to @Strawberry for the simplicity tips.

I'm a bit confused. Is this a long-winded way of asking how to split a string on a given character? E.g. SELECT SUBSTRING_INDEX('306465-14','-',1)m,SUBSTRING_INDEX('306465-14','-',-1) n; — Strawberry
@Strawberry Yes I think so, now I tidied the question, I think you probably answered it — RiggsFolly
UPDATE my_table SET m = SUBSTRING_INDEX(x,'-',1), n = SUBSTRING_INDEX(x,'-',-1); — Strawberry

Strawberry Strawberry · Accepted Answer · 2020-01-29T17:18:53

Consider the following example, which has the advantage of being both minimal and complete...

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(x VARCHAR(20) NOT NULL
,m INT NULL
,n INT NULL
);

INSERT INTO my_table VALUES
('306465-14',NULL,NULL),
('306465-15',NULL,NULL);

UPDATE my_table SET m = SUBSTRING_INDEX(x,'-',1), n = SUBSTRING_INDEX(x,'-',-1); 

SELECT * FROM my_table;
+-----------+--------+------+
| x         | m      | n    |
+-----------+--------+------+
| 306465-14 | 306465 |   14 |
| 306465-15 | 306465 |   15 |
+-----------+--------+------+

MySQL Separate 1 column VARCHAR into 2 columns of type INT. I've Error 1292 Truncated incorret DOUBLE value

1 Answers