Search code examples
sqlsql-serverpatindex

Check a word starting with specific string [SQL Server]

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
Solution

  • Simply add a leading space to the strings to ensure that you always find only the beginning of a word:

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

SET @Str = 'Dhaka is the capital of Bangladesh'
SET @SearchText = 'Dhaka Capital'
SET @Result = CHARINDEX(' ' + @SearchText, ' ' + @Str)

PRINT @Result -- print 14 here

    I have tested the above query against your test cases and it seems to work.