查询状态多选
Oracle语句: Where (v_StateID = 0 OR To_Number(UTL_RAW.BIT_AND(To_Char(Power(2, StateID), 'fmxxxxxx'), To_Char(v_StateID, 'fmxxxxxx'))) > 0)
SQL 语句: Where @StateID = 0 OR (Power(2, StateID) & @StateID) > 0
C# 语句:
string[] keysArray = Keys.Split(';');
for (int i = 0; i < keysArray.Length; i++)
{
stateID += (int)Math.Pow(2, NullHelper.GetInt32(keysArray[i]));
}
给定查询参数:
@StateIDList = '2,8,33'
SqlServer语句为:
where charindex( (',' + cast(StateID as nvarchar(50)) + ','), (',' + @StateIDList + ',') ) > 0
Oracle中相应为:
v_StateIDList = '2,8,33'
where instr( (',' || v_StateIDList || ','), (',' || to_char(StateID) || ',') ) > 0
需要特别注意的是,charindex和instr两个函数中,参数的顺序是正好相反的。