Solved

Like Operator Fails in Sybase ASA 9.0

Posted on 2004-08-12
11
764 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
When we talk about DevOps toolchains, I sometimes wonder how many people really get what we’re talking about. I don’t know if it’s just semantics or tone or something else, but sometimes I think it just sounds like buzzword sausage. So it’s always …
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

920 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

16 Experts available now in Live!

Get 1:1 Help Now