Yes,i understand it is no problem,my mistake.
One another thing is how does reversing of value and column work,
i mean the internals of it. Does it create any temp table ...
And i see the sqlite documentation of like, which say if the first char is not
a wild char then index
will be helpful.
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]
regard
ragha
******************************************************************************************
This email and its attachments contain confidential information from HUAWEI,
which is intended only for the person or entity whose address is listed above.
Any use of the information contained herein in any way (including, but not
limited to, total or partial disclosure, reproduction, or dissemination) by
persons other than the intended recipient(s) is prohibited. If you receive this
e-mail in error, please notify the sender by phone or email immediately and
delete it!
*****************************************************************************************
----- Original Message -----
From: Dennis Cote <[EMAIL PROTECTED]>
Date: Wednesday, August 29, 2007 8:45 pm
Subject: Re: [sqlite] BestMatch and SqliteStatment Clash
> 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]
> --------------------------------------------------------------------
> ---------
>
>
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
