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?

While asking a question, you need to provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example): (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). — Yitzhak Khabinsky, Dec 30 '21 at 16:57
I know that your solution is much easier, but I have to use these two functions according to the task. — Kevin, Dec 30 '21 at 17:09
It is quite an unnatural way of expressing that requirement but I assume the point of the task is just so you demonstrate understanding of the functions and maybe learn about them if new to you. So job done — Martin Smith, Dec 30 '21 at 17:50
`UPPER` may not be necessary depending on database collation (case sensitive or insensitive) — Alex, Dec 31 '21 at 00:16
So what's wrong with your current query? It's inneficient but works, if you want to use both functions. Just testing patindex > 0 would suffice. — Stu, Jan 01 '22 at 21:09

Maurice Pelchat · Answer 1 · 2022-05-04T21:37:22.507

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

SQL Server : finding substring using PATINDEX function

1 Answers1