[SOLVED] How to search using similar characters in random positions in Microsoft SQL?

How to search using similar characters in random positions in Microsoft SQL?

I'm looking for a query that would allow the user to use a variation of characters while searching for a result. The character positions are completely random. We use special characters È,Š,Ć,Č,Ž and Đ so all of the variations have to match, because most of users do not know how to spell correctly.

Example:

MISIC
MISIĆ
MISIČ
MIŠIC
MIŠIĆ
MIŠIČ

Solution

You can search it by using COLLATE

SELECT *
FROM TableNAme
WHERE
   columnName COLLATE Like '%MISIC%' COLLATE Latin1_general_CI_AI

latin1 makes the server treat strings using charset latin 1, basically ascii.
CI specifies case-insensitive, so "ABC" equals to "abc".
AI specifies accent-insensitive,so 'ü' equals to 'u'.

for more information collation go through the Collete

refereance : @JINO SHAJI

as per @Adephx comment this is working as expected with few modification

SELECT * FROM [TABLE] WHERE [COLUMN] LIKE '%NAME%' COLLATE Latin1_general_CI_AI