Try

SELECT PATINDEX('%[[]%','Here come the [dog]')
-- or
SELECT CHARINDEX('[','Here come the [dog]')

>I've tried to following:
>
>SELECT PATINDEX('%[%','Here come the [dog]'), and it returns 0, should return 15.
>
>If I replace the [ with < it's working fine, so I guess there is some concerns using [.
>
>Since the usage of [ in string is required, is there a way to permit the usage of [ in PATINDEX (escape sequence).
>
>Thanks!