Morning!

This is more of a "how can this be better" question, as to my knowledge the query below functions as expected.

Overall, I am trying to make a query (TSQL allowed) that takes a user input value (@Input) and sees if it exists in dbo.Table, ignoring prefix characters.

e.g. T.Value = FOOBAR
@Input = FOOBAR (true)
@Input = AFOOBAR (true)
@Input = FOORBARA (false)
@Input = FFOOBAR (true)
@Input = FOOBARR(false)
@Input = F (false)
etc..

Fairly confident the below works, however, I wanted to get other opinions as I am curious if a better/more efficient solution exists.

SELECT *
FROM dbo.Table AS T
WHERE CHARINDEX(REVERSE(T.Value), @Input, 0) > 0
AND @Input LIKE REVERSE(T.Value) + '%'

Thanks,

What is the CHARINDEX used for in the query above?

That is actually a good point.. I since added the LIKE check to fix a case that I found out about, and apparently that rendered the CHARINDEX condition useless as well.

Just using

SELECT *
FROM dbo.Table AS T
WHERE @Input LIKE REVERSE(T.Value) + '%'

Seems to still give all the results I expect.. Though regardless is using REVERSE() and LIKE a acceptable route to take or is there something more efficient out there?

How do you know what PART of @Input you want to look at?

Colour me confused.
Why the reverse anyways?

I'm assuming the leading values in the variable


How do you know there are no trailing values (suffix I guess)

How do you prevent that?

Is it always only 1 prefix char?