英文:
Oracle include NULL containing rows when compared string is zero length
问题
SELECT FIRSTNAME, LASTNAME FROM EMPLOYEES WHERE FIRSTNAME LIKE '%' || P_FIRSTNAME || '%' OR (P_FIRSTNAME = '' AND FIRSTNAME IS NULL);
英文:
I am building a function in Oracle where I would like to return a set of results based on input parameters.
Minimal select query
SELECT FIRSTNAME, LASTNAME FROM EMPLOYEES WHERE FIRSTNAME LIKE '%' || P_FIRSTNAME || '%';
where P_FIRSTNAME is a parameter coming from the function call.
This works mostly fine, however in case the incoming parameter is an empty string, I would like the select clause to also return rows where FIRSTNAME is NULL. The real query will have more conditions in the where clause.
答案1
得分: 1
添加另一个条件：
select *
from employees
where :p_firstname is null --> this
or firstname like '%' || :p_firstname ||'%';
英文:
Add another condition:
select *
from employees
where :p_firstname is null --> this
or firstname like '%' || :p_firstname ||'%';
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论