Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

Optimising “Ends With” searches with REVERSE Expand / Collapse
Author
Message
Posted Friday, January 15, 2010 5:27 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, September 22, 2014 6:07 AM
Points: 492, Visits: 362
Calvin Lawson (1/15/2010)
Thanks Ben, great idea for when you absolutely have to have those fast lookups with wildcards at the beginning of the search term. Which happens more often then one might think...

One small point; for the sake of code clarity, I would do something like this:

SELECT NAME FROM TEST_TABLE WHERE NAME_REVERSED LIKE REVERSE('%ID')


Hi Calvin,

True - I suppose it depends on whether you can get the calling code to reverse the search term for you. If you have a high volume web site doing lots of searches for example, you might want to get the web code to do this (albeit minor) string reversion as SQL is probably going to be slower at doing this than a web farm.

On the other hand it makes it better for code isolation purposes if you can just give the search term you are looking for to the SQL code

So another 'it depends' answer then!

Regards,
Ben
Post #848185
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse