Solved

sybase asa 7 (Like) limitation ?

Posted on 2009-07-14
9
551 Views
Last Modified: 2012-05-07
Hi,

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:
WHERE
  document_text LIKE '%{\pict%'
OR
  length of field is bigger then limitation

Any help would be appreciated

Regards


SELECT

		patientid,

		createdate,

		document_text

		

	FROM

		patient_document_text

		

	WHERE

		document_text LIKE '%{\pict%'

	AND

		patientid = 17731

Open in new window

0
Comment
Question by:msd_informatique
  • 3
  • 3
  • 2
9 Comments
 
LVL 24

Expert Comment

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

WHERE document_text LIKE '%{\\pict%'
0
 
LVL 23

Expert Comment

by:Kamaraj Subramanian
ID: 24857621
0
 
LVL 23

Expert Comment

by:Kamaraj Subramanian
ID: 24857622
0
 
LVL 3

Author Comment

by:msd_informatique
ID: 24858295
Hi,

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
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 24

Assisted Solution

by:Joe Woodhouse
Joe Woodhouse earned 50 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?
 
0
 
LVL 3

Accepted Solution

by:
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.

Regards
0
 
LVL 3

Author Comment

by:msd_informatique
ID: 25050689
See previous comment
0
 
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.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Agreggate sums in sql 1 518
My first ASE 15.7 developer edition installation. 12 2,013
MS SQL Linked server 3 117
SyBase Query Syntax Case When 7 107
In this article, I will show you HOW TO: Suppress Configuration Issues and Warnings Alert displayed in Summary status for ESXi 6.5 after enabling SSH or ESXi Shell.
Is your company's data protection keeping pace with virtualization? Here are 7 dynamic ways to adapt to rapid breakthroughs in technology.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

746 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now