In MySQL, how do I insert 1 when string value is 'true', 0 when false, while preserving nulls
Mysql has an IF operator which is similar to the ?: operator in most languages. IF(cond, val_if_true, val_if_false)
INSERT INTO target(SomeCol, BooleanCol)
SELECT SomeCol, IF(BooleanCol IS NOT NULL , IF(BooleanCol = 'true' , 1 , 0) , null)
FROM source
![StormeHawke](https://i.stack.imgur.com/bkseE.jpg?s=256&g=1)
StormeHawke
Been a lurker on SO for years... in 2012 I put up my first question, and have been gradually increasing in involvement ever since. SO is a great resource!
Updated on June 04, 2022Comments
-
StormeHawke about 2 years
I am using an
INSERT
query thatSELECT
s from a plain-text database into a more query-friendly database. The following is simplified a lot to prevent confusion with unnecessary information, if I've simplified too far for clarity please let me know and I will amend.Source data appears as follows:
| id (integer) | SomeCol (varchar) | BooleanCol (varchar) | ---------------------------------------------------------------------------- | 1 | 'String Data' | 'true' | | 2 | 'Other Data' | 'false' |
The target table needs to look as follows after my
INSERT
query:| id (integer) | SomeCol (varchar) | BooleanCol (tinyint(1)) | ------------------------------------------------------------------------------- | 1 | 'String Data' | 1 | | 2 | 'Other Data' | 0 |
I'm looking for something that would work more or less like this, borrowing a bit from java syntax:
INSERT INTO target(SomeCol, BooleanCol) SELECT SomeCol, (BooleanCol IS NOT NULL ? (BooleanCol == 'true' ? 1 : 0) : null) FROM source
Note that the java syntax provided as an example of what I want is intended to preserve
null
s as needed.Also, while I'm here, if you happen to know how
SELECT
the current time stamp from MySQL that would also be helpful.