Solved

sybase asa 7 (Like) limitation ?

Posted on 2009-07-14
9
553 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
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 
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
 
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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Cannot create ODBC connection over the network 3 1,214
Quickbooks Database Server Manager services crashes when file is opened 15 3,609
Clean up a mailbox 5 156
Need help with SYBASE syntax 5 83
When we purchase storage, we typically are advertised storage of 500GB, 1TB, 2TB and so on. However, when you actually install it into your computer, your 500GB HDD will actually show up as 465GB. Why? It has to do with the way people and computers…
Knowing where your website is hosted is as important as the features you receive, the monthly fee, and the support you receive. Due diligence should be done when choosing your next hosting provider.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

776 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