Solved

Like Operator Fails in Sybase ASA 9.0

Posted on 2004-08-12
11
779 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
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
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
Ransomware is a malware that is again in the list of security  concerns. Not only for companies, but also for Government security and  even at personal use. IT departments should be aware and have the right  knowledge to how to fight it.
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…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

821 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