Like Operator Fails in Sybase ASA 9.0

Posted on 2004-08-12
Last Modified: 2008-02-01
Problem with Sybase ASA

We have a table a_account_master having  columns
company_no N(3),
acct_key C(10)
and contains the Following data.

company_no  acct_key
1                  1114110
2                  2114110

Select * from a_account_master where acct_key like ‘%114110’;
Returns only one row

company_no  acct_key
2                   2114110                not 1114110. Why?

The above query works well with ASA 7.0 and returns
company_no  acct_key
1                  1114110
2                  2114110

Any comments? Appreciate your early response.
Thanks in advance.
Babu Abraham

Question by:babuabi
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +2
LVL 14

Expert Comment

by:Jan Franek
ID: 11786381
Well, it looks strange. Are you sure, that acct_key doesn't contain any "invisible" characters (such as new line) at the end ? If it's not the case, then only thing I can think of is some sort of data or index corruption. I'd try to create new table with the same structure, copy the data from original table and try to run the same query on this new table. If the problem persist, I'd try to contact Sybase technical support.
LVL 29

Expert Comment

ID: 11787428
What is the data type of the acct_key field?  I am not sure the LIKE operator works on non text fields.


Author Comment

ID: 11788067
acct_key is char(10).

BTW, I tried the test with asademo.db also. in the table ‘customer’ I added a row with fname = ‘1114110’ and tried the following query.

Select * from customer where fname like ‘%114110’;
No rows retrieved.

But the following queries worked.
Select * from customer where fname like ‘%14110’;
Select * from customer where fname like ‘%1114110’;
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

LVL 29

Expert Comment

ID: 11788139

Select * from customer where fname like ‘%114110%’;

If this works then it is definently added characters issue.

LVL 14

Expert Comment

by:Jan Franek
ID: 11788243
If Select * from customer where fname like ‘%114110’; didn't work and Select * from customer where fname like ‘%14110’; and
Select * from customer where fname like ‘%1114110’; worked, it's not a problem with invisible character at the end.

Are you sure, that there is no typo, like lowercase L instead of 1 ?

Did you try my suggestion about moving data into new table ? I'd also try to test, if the same behaviour occurs with varchar(10) instead of char(10).

Author Comment

ID: 11788393
I tried creating a new table (ABCD) with same structure as a_account_master and inserted only one record with acct_key '1114110'. Still no luck.

LVL 14

Expert Comment

by:Jan Franek
ID: 11789358
Try to use varchar instead of char

Expert Comment

ID: 11791622
Tetsing with both VARCHAR and CHAR, the only time I could reporduce this was by inserting a line feed/carriage return:

CREATE TABLE #Test (cname CHAR(10), vname VARCHAR(10))
56", "114
union select "%456", "%456"
union select "!456","!456"
union select "$456","$456"
union select "456%456","456%456"
union select "4567","4567"
union select "456%","456%"
union select "4567%","4567%"

SELECT * from #Test where cname like "%456"
SELECT * from #Test where vname like "%456"

--select * from #Test

The only column which fails the check when it looks they should pass is the first values enetered (namely with lf/cr)

Author Comment

ID: 11793837
This problem occurs only if enter the data having 7 characters or more beginning with 111, like '1114110', '11114110'.
If the string size is less than 7 characters the query works fine. Try using exactly the same data I used above. Then try the select statement 'Select * from Test where cname like '%114110'.
Anyway, I have already openned a support ticket with sybase. Let's see. I think this is the problem with ASA 9.0.1 version.


Accepted Solution

modulo earned 0 total points
ID: 12096722
PAQed, with points refunded (250)

Community Support Moderator

Featured Post

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

This article outlines the struggles that Macs encounter in Windows-dominated workplace environments – and what Mac users can do to improve their network connectivity and remain productive.
We aren’t perfect, just like everyone else.  Check out the email errors our community caught and learn the top errors every email marketer should avoid.
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Suggested Courses

630 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