Using LIKE with only a trailing % is usually slower than using SUBSTR

I cannot speak for SQL Server but with MariaDB LIKE is optimised to use indexes as long as the first character is not a wildcard - '%' or '_'

See documentation