我如何检查从同一表的另一列派生的MySQL列元素的有效性?

Question

例如，如果我在一列中有一个Project_ID = 123456并且Project_Name = R123456。我想检查Project_Name是否在每个元素中正确输入前缀R。我可以使用什么查询?

Answer 1

select Project_ID,
case when Project_Name = concat('R', Project_ID) 
then 'ok' 
else 'fail'
end as result
from your_table

或者只得到不正确的:

select Project_ID
from your_table
where Project_Name <> concat('R', Project_ID)