How to Include Punctuation in Like operator

Pinku

I have below data in Database: This is an example only. Data may be change
sustainability- energy
sustainability. energy
sustainability/ energy
sustainability; energy
sustainability* energy
sustainability: energy
sustainability, energy
sustainability energy

Current query is : select * from Table where description Like '%sustainability energy%'
I want to write a query which will return all above data .It should not be like '%sustainability%' because we dont what data may come. just an example.sustainability energy is a user search data.If it contain special character then also it should find.

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Naveen Kumar1y ago
Accepted answer
You can use PATINDEX and LIKE:
- PATINDEX looks for patterns, where [^a-zA-Z0-9 ] represents any non-alphanumeric character (except space) between "sustainability" and "energy."
- LIKE is used to capture the simple case of "sustainability energy" without any special character in between.
Check the below query
```
SELECT * FROM Title
WHERE PATINDEX('%sustainability[^a-zA-Z0-9 ]%energy%', description) > 0
   OR description LIKE '%sustainability energy%'
```
+5
Pinku1y ago
Thanks Naveen :)
+3