RaghavendraK 70574 wrote:
Hi,
There are 2 testcases one works and other fails
Hope am clear.
SqliteVersion: 3.4.0
TestCase 1: works
create table test(t text);
insert into test values ('9');
insert into test values('98');
insert into test values('983');
insert into test values('9854');
select * from test where '982' like t || '%' order by t desc limit 1;
output: 98 [correct]
TestCase 2: does not work
create table 'tbl.7'(ver integer,
column1 text not NULL,
column2 text not NULL,
column3 text not NULL,
column4 text not NULL,
column5 text not NULL,
column6 text not NULL,
column7 text not NULL,
column8 text not NULL,
column9 text not NULL,
column10 text not NULL,
primary key(ver,column1,column2,column3,column4,column5));
insert into 'tbl.7'
values
(7, '9845002655', '1', '1', '1', '1','x','x','x',
'x','x');
insert into 'tbl.7'
values
(7, '9855002655', '1', '1', '1', '1','x','x','x',
'x','x');
--Best match for 985 shd be 9855002655
select * from 'tbl.7' where '985' like column1 || '%' order by column1 desc
limit 1;
output: none //this is a bug.
'9854002656%' is not a match for '982', so seems not to be a bug
As has been pointed out several times already your expectations are wrong.
You need additional code to implement your best match criterion. What
you might want to match is the substring of the column up to the length
of the target string, not the entire column.
select * from tab
where :target like substr(column1,1,length(:target)) || '%'
order by column1 desc limit 1;
I'm not sure why you think the first such match is the best match, but
that is another issue for you to look at.
Another point, you should be using double quotes around your table name,
not single quotes.
HTH
Dennis Cote
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
