Go Premium for a chance to win a PS4. Enter to Win


sybase asa 7 (Like) limitation ?

Posted on 2009-07-14
Medium Priority
Last Modified: 2012-05-07

I have a very small and simple query (see snippet below)

This query works fine except when the "{\pict" entry is not into the xxx caracters from the start in the text field.

There seems to be a limitation where "like" would search only in the xxx first chars of the field.

1- Is there a way to search in the whole field even if it means scanning all the rows one by one ?
2- If there is no way, how can I see/change the limit ?

I would like to add a condition like this:
  document_text LIKE '%{\pict%'
  length of field is bigger then limitation

Any help would be appreciated


		document_text LIKE '%{\pict%'
		patientid = 17731

Open in new window

Question by:msd_informatique
  • 3
  • 3
  • 2
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 24857297
I think your backslash is being interpreted. Force a literal:

WHERE document_text LIKE '%{\\pict%'
[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.


Author Comment

ID: 24858295

I just tested it out and it doesnt change anything...
With or without the backslash it doesn't work.

I have 4 entries in my bd matching but only 3 of them are returned.
The 3 returned one have the searched sequence near the top
The fourth one, I added a lot of content at the beginning and the search pattern arrives later in the content...

I really think it has something to do with an index or something cause If I search my entire db (22194 rows are returned in less than a second). There is no way the database could search in 100 gigs and return the result that fast.

I think the search is made in the index for faster processing, but that the index is containing only the n first byte of data
LVL 24

Assisted Solution

by:Joe Woodhouse
Joe Woodhouse earned 200 total points
ID: 24861052
itkamaraj: Your link is to a manual for ASE 12.5.x. This question is about ASA 7, a very different product with different syntax. :)

msd_informatique: I am not aware of any behaviour along the lines of your speculation, nor do the ASA manuals suggest anything similar. I think you are wrong - the index is not limited to the first n bytes of data. If this were true then we could not use indexes to implement UNIQUE constraints as only the first n bytes would be unique.

In terms of query speed remember there are other WHERE clauses the ASA optimiser can use. In your example above we need consider only rows where patientid = 17731, which is presumably a small subset of the data. Once we have those rows we can then start searching the string.

One idea I have - is your database created case sensitive? If so, your LIKE pattern would not match every possible match. You would need something like this:

WHERE document_text LIKE '%{#\[Pp][Ii][Cc][Tt]%' ESCAPE '#'
I'm also using the ESCAPE syntax to guarantee the backslash isn't an issue here.

Can you show us the exact values of document_text for the 3 rows that do match and the 1 row that doesn't?

Accepted Solution

msd_informatique earned 0 total points
ID: 25050684
Hi, Sorry for my lack of response but comments were not was I was looking for...

I really think there is some kind of index limitation because like I said, searching 10 gigs of data whithout the "AND patientid = 17731" returns a result in less then a second and a "like %something%" should go through all the rows one by one and there is no way to go through 10 gigs of data un less then a second (not yet lol)

It's not a case of case sensitivity neither as the same text string is found when in the first 4k of data while the same string is not found after the first 4k of data. Like Joe said, there is no mention of it in the manual so it remains a total mystery to me. I ran the script I was needing on

Select * and searching for the pattern in the programming language so it was less optimal, but at least, it worked...

I will give some points to "Joe_Woodhouse" as his answer was the most pertitent but this question remains unresolved.


Author Comment

ID: 25050689
See previous comment
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 25050727
If I didn't answer the question then I shouldn't get any points. :) Accepted answered are used by people searching the site, and I think I agree while I've asked some useful questions we didn't get to the bottom of this. I'd agree with delete no refund, or delete with refund.

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Mailbox Corruption is a nightmare every Exchange DBA wishes he never has. Recovering from it can be super-hectic if not entirely futile. And though techniques like the New-MailboxRepairRequest cmdlet have been designed to help with fixing minor corr…
Ranking ecommerce websites is a vital process. You need to have a strong SEO (Search Engine Optimization) strategy. If you don’t have one, you are losing out on brand impressions, clicks and sales. Check this guide on how to improve website traffic …
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Suggested Courses

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question