sybase asa 7 (Like) limitation ?

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

LVL 3
msd_informatiqueAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Joe WoodhousePrincipal ConsultantCommented:
I think your backslash is being interpreted. Force a literal:

WHERE document_text LIKE '%{\\pict%'
0
Kamaraj SubramanianApplication Support AnalystCommented:
0
Kamaraj SubramanianApplication Support AnalystCommented:
0
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

msd_informatiqueAuthor Commented:
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
Joe WoodhousePrincipal ConsultantCommented:
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
msd_informatiqueAuthor Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
msd_informatiqueAuthor Commented:
See previous comment
0
Joe WoodhousePrincipal ConsultantCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Sybase Database

From novice to tech pro — start learning today.