Solved

Like Operator Fails in Sybase ASA 9.0

Posted on 2004-08-12
11
752 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
  • 3
  • 3
  • 2
  • +2
11 Comments
 
LVL 14

Expert Comment

by:Jan_Franek
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 29

Expert Comment

by:leonstryker
Comment Utility
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
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:babuabi
Comment Utility
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
Comment Utility
Try to use varchar instead of char
0
 
LVL 5

Expert Comment

by:hkamal
Comment Utility
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
Comment Utility
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
Comment Utility
PAQed, with points refunded (250)

modulo
Community Support Moderator
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

HOW TO: Connect to the VMware vSphere Hypervisor 6.5 (ESXi 6.5) using the vSphere (HTML5 Web) Host Client 6.5, and perform a simple configuration task of adding a new VMFS 6 datastore.
Find out what Office 365 Transport Rules are, how they work and their limitations managing Office 365 signatures.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

771 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

11 Experts available now in Live!

Get 1:1 Help Now