Most of the time when I need to query rows, I would use column name and LIKE operator. However, CHARINDEX operator can provide a quick way to check if a particular word or special character is present in the column. CHARINDEX identifies numeric location of word or character in the string. Here is a quick way to check if a column contains name Alex.
(CASE WHEN CHARINDEX(‘Alex’, ColumnName) > 0 THEN 1 ELSE 0 END) AS [Contains equivalent],
Such pattern can be useful in the SELECT part of the query or in the WHERE clause. It is very simple and can be used to identify and query certain rows. Also, I used a similar pattern to check our date table for Monday vs the rest of the week. On Mondays, I had another query to fetch 3 prior days (Friday, Saturday and Sunday), while the rest of the week the query would bring the prior day.
Another common way CHARINDEX is used is for identifying delimiters and then selecting data left or right of delimiter. In the example below, CHARINDEX determines numerical position of the delimiter ‘-‘. Then -1 moves the identifier to the character before the delimiter. Finally, LEFT, picks up all character prior to the delimiter.
LEFT(String, CHARINDEX(‘-‘, String)-1)
