How to find data with punctuation using Like operator in SQL server

Question

sustainability- energy
sustainability. energy
sustainability/ energy
sustainability; energy
sustainability* energy
sustainability: energy
sustainability, energy
sustainability energy
select * from Title where TitleDescription like '%sustainability energy%'
.So if i run above query its getting the last record only but i need all

Muhammad Imran Ansari · Accepted Answer

Hi Pinku,
To retrieve all the variations of the phrase “sustainability energy” in your data, you need to mention the punctuation between the words. Since SQL Server’s LIKE operator does not treat punctuation as equivalent to a space, you'll need to include all possible punctuation marks that could appear between “sustainability” and “energy.”
You can try the below query:
SELECT *
FROM Title
WHERE (TitleDescription LIKE '%sustainability[ .,/;:*,-] energy%'
OR TitleDescription LIKE '%sustainability energy%')
Thank you.

Muhammad Imran Ansari · Answer

If you want to get all without punctuations then you can use a combination of REPLACE functions to remove specific punctuation characters from the TitleDescription field.

Pinku · Answer

Thanks Imran its working.But i have another query.
sustainability- energy
sustainability. energy
sustainability/ energy
sustainability; energy
sustainability* energy
sustainability: energy
sustainability, energy
sustainability energy
lets say there are some data like above.  and i want to show all of this but with out Punctuation like below . how to do it.
sustainability energy
sustainability energy
sustainability energy
sustainability energy
sustainability energy
sustainability energy

Rohit Kumar Srivastava · Answer

Hi you can use _ like below.
select * from Title where TitleDescription like '%sustainability _ energy%'
hope it will help you.