Sybase WHERE clause for "starts with" <string>

veryangryman

I have a very long and cumbersome set of strings in my application. I want to uniquely identify some by tagging the current count of seconds since epoch to the start of each string. Sofar sogood.

Problem is that when i query them with LIKE, the query takes a lot of processing.

[HTML]SELECT SomeAttribute FROM SomeTable WHERE Attribute LIKE '%SomeString%'[/HTML]

Is there a way to do a "starts with" query in sybase? Even roundabout would be okish.

Procasinator

veryangryman wrote: »

I have a very long and cumbersome set of strings in my application. I want to uniquely identify some by tagging the current count of seconds since epoch to the start of each string. Sofar sogood.

Problem is that when i query them with LIKE, the query takes a lot of processing.

[HTML]SELECT SomeAttribute FROM SomeTable WHERE Attribute LIKE '%SomeString%'[/HTML]

Is there a way to do a "starts with" query in sybase? Even roundabout would be okish.

Substring?
http://infocenter.sybase.com/archive/index.jsp?topic=/com.sybase.infocenter.dc38151.1260/html/iqref/Substr.htm

veryangryman

Procasinator wrote: »

Substring?
http://infocenter.sybase.com/archive/index.jsp?topic=/com.sybase.infocenter.dc38151.1260/html/iqref/Substr.htm

Just trying to fit that into my query but its not syntax-kosher.

SELECT SomeAttribute FROM SomeTable WHERE Attribute SUBSTRING '%someString%'

Giblet

Interesting fact, some databases support index lookups on like matching if it's in the following format

SELECT SomeAttribute FROM SomeTable WHERE Attribute LIKE 'SomeString%'

Seems to be true for Sybase
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc38151.1520/html/iqrefbb/CACGCGGC.htm
Remove the first '%' and check it out.

Procasinator

veryangryman wrote: »

Just trying to fit that into my query but its not syntax-kosher.

SELECT SomeAttribute FROM SomeTable WHERE Attribute SUBSTRING '%someString%'

Looking at your query and your question, you say starts with, but use '%Something%' as you pattern. Shouldn't the pattern be 'Something%' if it is the beginning of the string?

If so, your query would be something like:

SELECT SomeAttribute FROM SomeTable WHERE SUBSTRING(SomeAttribute, 1, 9) = 'Something'

where 9 is the length of the string you are looking from, and 1 is where to search from (first character).

I don't know whether it performs better, worse or the same as the equivalent LIKE on Sybase. Test it for your usage.

oscarBravo

A decent database engine should be able to query efficiently on a LIKE 'Something%' query - if there's an index on the Attribute column. A LIKE '%Something%' will always require a table scan and can't be optimised.