SQL Server : finding substring using PATINDEX function

Question

I'm writing different queries in SQL Server.

I have 2 tables, Employees and Departments. Table Employees consists of EMPLOYEE_ID, ENAME, ID_DEP - department id. Table Departments consists of ID_DEP, DNAME. The task is to show Employee.ENAME and his Department.DNAME where Department.DNAME has word Sales inside. I have to use functions SUBSTRING and PATINDEX.

Here is my code, but I think that it looks quite strange and it's meaningless. Nevertheless I need to use both functions in this task.

SELECT e.ENAME, d.DNAME 
FROM EMPLOYEE e
JOIN DEPARTMENTS d ON d.ID_DEP = e.ID_DEP
WHERE UPPER(SUBSTRING(d.DNAME, (PATINDEX('%SALES%', d.DNAME)), 5)) = 'SALES'

Any ideas what should I change while continuing using these two functions?

Answer 1

The answer is just below, and BTW, using row constructor VALUES is an excellent mean to get a simple demo of what you want.

The query below provides several possible answers to your ambiguous question. Why would you need to use these functions? Is it an homework that specify this? If your SQL Server database was installed with a case insensitive collation, or the column 'name' was set to this collation, no matter how UPPER is used, it will makes no difference in match. The most you can get of UPPER is to make the data appears uppercase in the result, or turn data to uppercase if you update the column. PATINDEX/LIKE are going to perform case insensitive match. And you know, this is so useful, that most people configure their server with some case insensitive collation. To circumvent default comparison behavior that match the column/database collation, specify the collate clause, as in the outer apply of Test2.

Here are the queries. Watch the results, they show what I said.

select *
From
  (Values ('très sales'), ('TRES SALES'), ('PLUTOT PROPRE')) as d(name)
  outer apply (Select Test1='match' Where Substring(name, patindex('%SALES%', name), 5) = 'SALES') as test1
  outer apply (Select Test2='match' Where name COLLATE Latin1_General_CS_AS like '%SALES%' ) as test2 -- CS_AS mean case sensitive
  outer apply (Select Test3='match' Where name like '%SALES%') as test3

select * -- really want an upper case match ?
From
  (Values ('très sales'), ('TRES SALES'), ('PLUTOT PROPRE')) as d(name)
Where name COLLATE Latin1_General_CS_AS like '%SALES%'

select * -- demo of patindex
From
  (Values ('très sales'), ('TRES SALES'), ('PLUTOT PROPRE')) as d(name)
  outer apply (Select ReallyUpperMatch=name Where patindex('%SALES%', name COLLATE Latin1_General_CS_AS)>0 ) as ReallyUpperMatch -- CI_AS mean case sensitive
  outer apply (Select ciMatch=name Where name like '%SALES%' ) as ciMatch
  outer apply (Select MakeItLookUpper=UPPER(ciMatch) ) MakeItLookUpper