Oracle "Starts With" text join, excluding nulls

Question

I am having some trouble. I am trying to build a SQL query that uses "starts with" logic. A little background first...

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".

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.

Thank you in advance for your help!

If that were an option, Bill, I wouldn't have written the question. :/ — ryvantage, Apr 07 '16 at 21:56

score 0 · Answer 1 · answered Apr 18 '13 at 21:54

0

Try this

select user.name 
from user user
inner join salesperson spn 
on spn.salesperson_code like nvl(trim(user.employee_code),'-') || '%'

answered Apr 18 '13 at 21:54

rs.

26,707
12
68
90

1

I suggest using @AlvaroGVicario's response. NVL will *work* in this case, but I'm always concerned about introducing a function call into this sort of comparison - plus, using NVL obsfuscates the intent (which is to force a NULL to match nothing - and this might not even succeed if an EMPLOYEE_CODE value happens to start with '-'). Better IMO to use `LIKE USER.EMPLOYEE_CODE || '%' AND USER.EMPLOYEE_CODE NOT NULL`. YMMV. Share and enjoy. – Bob Jarvis - Слава Україні Apr 19 '13 at 01:30

score 0 · Answer 2 · edited Apr 19 '13 at 01:31

0

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 || '%')

edited Apr 19 '13 at 01:31

Bob Jarvis - Слава Україні

48,992
9
77
110

answered Apr 18 '13 at 21:54

Yahia

69,653
9
115
144

Hmm. Looking at the decode function, I don't want to test for null values, I want to test for empty strings. Maybe it should be this? `select user.name from user user inner join salesperson spn on spn.salesperson_code like DECODE (user.employee_code, '', NULL, user.employee_code || '%')` – ryvantage Apr 18 '13 at 22:08
There's no such thing as "empty string" in Oracle. – Álvaro González Apr 18 '13 at 22:09
Wow ok that's good to know. Why then wouldn't I just put an and employee_code is not null ? – ryvantage Apr 18 '13 at 22:11
I should clarify my statement. If you attempt to insert `''` into a column Oracle will helpfully accept the input but will store `NULL` instead. – Álvaro González Apr 18 '13 at 22:13
@user963076 as always there is usually more than one way to solve a given problem... the proposed query is just one possible solution... – Yahia Apr 18 '13 at 22:13
IMO using NVL, DECODE, or a even a CASE expression here is obfuscatory. `...AND USER.EMPLOYEE_CODE IS NOT NULL` is clear and easy to understand. Share and enjoy. – Bob Jarvis - Слава Україні Apr 19 '13 at 01:35

score 0 · Answer 3 · answered Apr 22 '13 at 01:22

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.

score 0 · Answer 4 · answered Feb 08 '17 at 05:57

0

Add an IS NOT NULL condition:

select *
from user
inner join salesperson spn
        on spn.salesperson_code like user.employee_code || '%'
       and user.employee_code is not null;

answered Feb 08 '17 at 05:57

Jon Heller

34,999
6
74
132

Oracle "Starts With" text join, excluding nulls

4 Answers4