Solved

sybase asa 7 (Like) limitation ?

Posted on 2009-07-14
9
552 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Sybase 15.7 database backup 8 608
Linked Server to connect using Advantage OLEDB from MS SQL 18 2,185
SyBase Query Syntax Case When 7 119
MS SQL 2008 server - system update recommendation 13 104
Scenario: Your operations manager has discovered an anomaly in your security system. The business will start to suffer within 15 minutes if it is a major IT incident. What should she do? We have 6 recommendations for managing major incidents (https:…
In 2017, ransomware will become so virulent and widespread that if you aren’t a victim yourself, you will know someone who is.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

910 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

25 Experts available now in Live!

Get 1:1 Help Now