Oracle“开头为”文本连接，不包括空值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16093724/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle "Starts With" text join, excluding nulls
提问by ryvantage
I am having some trouble. I am trying to build a SQL query that uses "starts with" logic. A little background first...
我遇到了一些麻烦。我正在尝试构建一个使用“开始于”逻辑的 SQL 查询。先介绍一下背景...
In the database that I've been tasked to write reports from, there is a "user" table and a "salesperson" table, with salespersons belonging to a user. In a not-so-brilliant move, the designer of the database decided to associate the salespersons through a substring match to their employee code. For example:
在我负责编写报告的数据库中，有一个“用户”表和一个“销售人员”表，销售人员属于一个用户。数据库的设计者决定通过子字符串匹配将销售人员与其员工代码相关联，这是一个不太聪明的举动。例如：
John Smith's "employee_code" would be "JS". But he has multiple "salespersons" to distinguish his different sale types. So he might have "JS1", "JS2", "JS3", etc., as his "salesperson_code".
John Smith 的“employee_code”将是“JS”。但是他有多个“销售人员”来区分他不同的销售类型。所以他可能有“JS1”、“JS2”、“JS3”等，作为他的“salesperson_code”。
To illustrate:
为了显示：
user table:
|----------|-----------|----------|---------------|
| username | firstname | lastname | employee_code |
|----------|-----------|----------|---------------|
| JSMITH | John | Smith | JS |
|----------|-----------|----------|---------------|
salesperson table:
|------------------|------------------|
| salesperson_name | salesperson_code |
|------------------|------------------|
| John Smith 1 | JS1 |
| John Smith 2 | JS2 |
| John Smith 3 | JS3 |
|------------------|------------------|
There is no foreign key on the salesperson table linking them to the user table, only the substring from the employee code.
销售人员表上没有将它们链接到用户表的外键，只有来自员工代码的子字符串。
I do not remember where I found this answer, but in my queries I've been doing this:
我不记得我在哪里找到了这个答案，但在我的查询中我一直在这样做：
select user.name
from user user
inner join salesperson spn on spn.salesperson_code like user.employee_code || '%'
This logic successfully does the "starts with" match. However, there are users with blank employee codes and they, also, match this query.
这个逻辑成功地完成了“开始于”匹配。但是，有些用户的员工代码为空，他们也匹配此查询。
What I am looking for: how do I modify this query so that if the employee_code is blank it will not match?I'm pretty newbie with Oracle queries. Other DBMS' have a starts with clause that will not match blank fields.
我在寻找什么：如何修改此查询，以便如果 employee_code 为空，它将不匹配？我是 Oracle 查询的新手。其他 DBMS 有一个不会匹配空白字段的以子句开头。
Thank you in advance for your help!
预先感谢您的帮助！
回答by rs.
Try this
尝试这个
select user.name
from user user
inner join salesperson spn
on spn.salesperson_code like nvl(trim(user.employee_code),'-') || '%'
回答by Yahia
try
尝试
select user.name
from user user
inner join salesperson spn
on spn.salesperson_code like DECODE (user.employee_code,
NULL, NULL,
user.employee_code || '%')
回答by Brian
I would suggest using a regular expression to extract the non-digit parts of the salesperson code and optionally the digits part. Create a view for the table with these added fields or use it as a table expression in the query.
我建议使用正则表达式来提取销售人员代码的非数字部分和可选的数字部分。使用这些添加的字段为表创建一个视图，或将其用作查询中的表表达式。
SELECT regexp_substr(salesperson_code,'\D+') AS employee_code,
regexp_substr(salesperson_code,'\d+') AS employee_sales_no,
salesperson_name, salesperson_code
FROM salesperson
Note: the regular expressions match one or more non-digits and one or more digits respectively.
注意：正则表达式分别匹配一位或多位非数字和一位或多位数字。
回答by Jon Heller
Add an IS NOT NULLcondition:
添加IS NOT NULL条件：
select *
from user
inner join salesperson spn
on spn.salesperson_code like user.employee_code || '%'
and user.employee_code is not null;
