CODEWITHSUNDEEP
mysqlsqlstrict
Merlin
Merlin

Reputation: 25639

MySQL using Strict Mode with subindex in where clause

Using MYSQL 5.5 in strict mode, I am getting: 

Error Code: 1292
Truncated incorrect DOUBLE value: 'C'

Using SQL below: 

INSERT INTO table.t2 SELECT Something FROM table.t3 
WHERE SUBSTRING_INDEX(Something,":",-1) NOT IN ("C","E")

Data in 'Something' column could be like this:

2131:2134
2132:2134:C
2133:2134:C
2134:2134:E
2135:2134:E
2136:2134
2137:2134
2138:2134
2139:2134

I know I could remove strict mode, but like to fix this. Its my first time using "cast" while trying to solve this.... -1, gives integer or string

Upvotes: 0

Views: 322

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

There is a problem in the where clause. The expression 'C' or 'E' is a boolean expression, which is treated as an integer. Hence, the error about converting a value to a number.

Try using not in instead:

WHERE SUBSTRING_INDEX(Something, ':', -1) NOT IN ('C', 'E')

Upvotes: 1

Related Questions