Check a word starting with specific string [SQL Server]

Question

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

Search Text: Dhaka, Result: 1
Search Text: tal, Result: 0
Search Text: Mirpur, Result: 0
Search Text: isthe, Result: 0
Search Text: is the, Result: 7
Search Text: Dhaka Capital, Result: 0

why create case condition. it may be CHARINDEX function given result — Mukesh Kalgude, Jun 08 '16 at 09:35
I need to check if any of the word is starting with the search text first, if yes then get the index by `CHARINDEX` otherwise result 0 — Mahedi Sabuj, Jun 08 '16 at 09:40
Your logic so far is not correct. Imagine a search of "cap" in "Bangladesh experiences uncapped growth in its capital". Your where condition will trigger on the " capital", but the `CHARINDEX` will find the "cap" in "uncapped" — TToni, Jun 08 '16 at 09:51
@TToni, Yes, You are correct. Need to fix the Issue. Looks like If I can, It will solve the issue i describe in the question. Thanks. — Mahedi Sabuj, Jun 08 '16 at 09:54

Serg · Answer 1 · 2016-06-08T10:10:22.830

1

To compute the function only once per row in SELECT make it table valued function. Or if it's impossible for some reason use CROSS APPLY

SELECT .. a, b,
FROM ..
CROSS APPLY (SELECT my_scalar_fn(a,b) as Str) arg
WHERE CASE WHEN arg.Str LIKE SearchText + '%'
                      OR arg.Str LIKE + '% ' + SearchText + '%' 
                   THEN CHARINDEX(SearchText, arg.Str)
              ELSE 0 END

edited Jun 08 '16 at 10:10

answered Jun 08 '16 at 10:03

Serg

22,285
5
21
48

Search Text is fix, I generate `Str` with the function – Mahedi Sabuj Jun 08 '16 at 10:08
Your answer solve my problem but Alex's answer also solve the problem TToni notice. +1 for you. Thanks. – Mahedi Sabuj Jun 08 '16 at 10:46

score 1 · Accepted Answer · answered Jun 08 '16 at 10:10

1

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.

answered Jun 08 '16 at 10:10

Alex

4,885
3
19
39

Looks like a easy and simple solution. – Mahedi Sabuj Jun 08 '16 at 10:17
2

A credit where a credit is due: http://stackoverflow.com/questions/34814908/using-case-in-where-statement-when-parameter-has-multiple-values – Alex Jun 08 '16 at 10:21

Check a word starting with specific string [SQL Server]

2 Answers2