Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Like Operator Fails in Sybase ASA 9.0

Posted on 2004-08-12
11
Medium Priority
?
796 Views
Last Modified: 2008-02-01
Problem with Sybase ASA 9.0.1.1785

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

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

0
Comment
Question by:babuabi
[X]
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
11 Comments
 
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.
0
 
LVL 29

Expert Comment

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

Leon
0
 

Author Comment

by:babuabi
ID: 11788067
Leon,
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’;
0
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

by:leonstryker
ID: 11788139
Try:

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

If this works then it is definently added characters issue.

Leon
0
 
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).
0
 

Author Comment

by:babuabi
ID: 11788393
Jan,
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.

Babu
0
 
LVL 14

Expert Comment

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

Expert Comment

by:hkamal
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))
INSERT #Test SELECT "114
56", "114
56"
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)
0
 

Author Comment

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


0
 

Accepted Solution

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

modulo
Community Support Moderator
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

OnPage is proud to be a Gold Sponsor at DevOps Days Boston 2017 taking place at the Cyclorama on September 18th  and 19th.  If you’re attending, please stop by booth 11 and get one of our cool give aways and enter to win one of the many prizes we’re…
Check out what's been happening in the Experts Exchange community.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

730 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