MySQL Convert a Substring_index and then check if it equals a value in another column

user_11824434 0
Viewed 18

I have a column that has a tibcoofferid in the format of 12345. Another column of contains tibcoaddevent in the format Add_12345.

I'd like an output column that checks if the tibcoofferid equals the number after the _ in the tibcoaddevent.

I have attempted the following, however the AddEvent check is being flagged as False, even though the two values match. Can anyone help flag what I am doing wrong?

SELECT tibcoofferid, tibcoaddevent
    , CONVERT(SUBSTRING_INDEX(tibcoaddevent,'_',-1),UNSIGNED INTEGER) AS num
    , IF (tibcoofferid = 'num', 'True', 'False') AS AddEvent
FROM offer_360 
WHERE tibcoofferid IN (13979, 13980, 13900, 13352, 12709)

Output:

tibcoofferid tibcoaddevent num AddEvent
12709 Add_12709 12709 False
13352 Add_13352 13352 False
13900 Add_13900 13900 False
13979 Add_13979 13979 False
13980 Add_13980 13980 False

Also, is it possible to do this without the 'num' column being output like in my attempt?

data-conversion if-statement mysql
1 Answers

Rephrase the logic to tibcoaddevent = Add_<tibcoofferid>:

SELECT
  tibcoofferid,
  tibcoaddevent,
  case when tibcoaddevent = concat('Add_', tibcoofferid) then 'True' else 'False' end AS AddEvent
FROM offer_360 
WHERE ...
user_2561960
Related