CODEWITHSUNDEEP
oracle-databasesql-likestartswith
ryvantage
ryvantage

Reputation: 13496

Oracle "Starts With" text join, excluding nulls

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!

Upvotes: 1

Views: 18536

Answers (4)

Jon Heller
Jon Heller

Reputation: 36912

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;

Upvotes: 0

Brian
Brian

Reputation: 7299

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.

Upvotes: 0

Yahia
Yahia

Reputation: 70379

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

Upvotes: 0

rs.
rs.

Reputation: 27467

Try this

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

Upvotes: 0

Related Questions