I try to search on a string like `Dhaka is the capital of Bangladesh` which contain six words. If my search text is `cap` (which is the starting text of capital), it will give me the starting index of the search text in the string (14 here). And if the search text contain in the string but not starting text any of the word, it will give me 0. Please take a look at the **Test Case** for better understanding.

**What I tried**

```
DECLARE @SearchText VARCHAR(20),
        @Str VARCHAR(MAX),
        @Result INT

SET @Str = 'Dhaka is the capital of Bangladesh'
SET @SearchText = 'cap'

SET @Result = CASE WHEN @Str LIKE @SearchText + '%'
                      OR @Str LIKE + '% ' + @SearchText + '%' 
                   THEN CHARINDEX(@SearchText, @Str)
              ELSE 0 END

PRINT @Result -- print 14 here

```

For my case, I need to generate `@Str` with another sql function. Here, we need to generate `@Str` 3 times which is costly (I think). So, is there any way so that I need generate `@Str` only one time? [Is that possible by using `PATINDEX`]

**Note:** `CASE` condition appear in the where clause at my original query. So, It is not possible to set the `@Str` value in variable then use it in the where clause.

**Test Case**

1. `Search Text`: **Dhaka**, `Result`: **1**
2. `Search Text`: **tal**, `Result`: **0**
3. `Search Text`: **Mirpur**, `Result`: **0**
4. `Search Text`: **isthe**, `Result`: **0**
5. `Search Text`: **is the**, `Result`: **7**
6. `Search Text`: **Dhaka Capital**, `Result`: **0**