Re: [sqlite] Check for existence of substring in table

[Ulrich Schöbel]

Igor Tandetnik wrote:
> "Ulrich Schöbel"
>  wrote in
> message news:4997ddb2.9070...@bmu.office-on-the.net
>> I have a very simple table 'friends' with only one column
>> 'link':
>>
>> create table friends (link text);
>>
>> Lets assume there are 2 rows, 'abc' and 'def'.
>>
>> Then there is a Tcl variable x containing a string. If $x
>> starts with either abc or def (if $x starts with any value
>> in the table) I want a TRUE value (or something
>> comparable) otherwise a FALSE.
> 
> select exists (select 1 from friends where ? like link || '%');
> 
> Igor Tandetnik 
> 

Wow! Nifty! Thanks Igor.

Regards

Ulli
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Check for existence of substring in table

[Igor Tandetnik]

"Ulrich Schöbel"
 wrote in
message news:4997ddb2.9070...@bmu.office-on-the.net
> I have a very simple table 'friends' with only one column
> 'link':
>
> create table friends (link text);
>
> Lets assume there are 2 rows, 'abc' and 'def'.
>
> Then there is a Tcl variable x containing a string. If $x
> starts with either abc or def (if $x starts with any value
> in the table) I want a TRUE value (or something
> comparable) otherwise a FALSE.

select exists (select 1 from friends where ? like link || '%');

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Check for existence of substring in table

[John Machin]

On 15/02/2009 9:15 PM, Ulrich Schöbel wrote:
> John Machin wrote:

>> all I know about Tcl is that I don't want to 
>> know any more about Tcl :-)
> 
> You should want to ;-)

You should want to be using Python instead of Tcl ;-)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Check for existence of substring in table

[Ulrich Schöbel]

John Machin wrote:
> On 15/02/2009 8:17 PM, Ulrich Schöbel wrote:
>> Hi all,
>>
>> I'm stuck with my problem. Hopefully someone here can help.
>>
>> I have a very simple table 'friends' with only one column
>> 'link':
>>
>> create table friends (link text);
>>
>> Lets assume there are 2 rows, 'abc' and 'def'.
>>
>> Then there is a Tcl variable x containing a string. If $x
>> starts with either abc or def (if $x starts with any value
>> in the table) I want a TRUE value (or something
>> comparable) otherwise a FALSE.
> 
> You haven't said whether you problem is writing the SQL or the Tcl or 
> both ...

SQL

> here's some SQL that will return 1 if any such link value 
> exists, else 0. Are you sure you don't want to know which link value 
> matches?

Yes.

> What happens if more than 1 link value matches -- do you care?

No.

> 
> 
> sqlite> create table f (link text);
> sqlite> insert into f values('abc');
> sqlite> insert into f values('def');
> sqlite> select * from f;
> abc
> def
> sqlite> select exists(select 1 from f where link = substr('defend', 1, 
> length(link)));
> 1
> sqlite> select exists(select 1 from f where link = substr('abcpqr', 1, 
> length(link)));
> 1
> sqlite> select exists(select 1 from f where link = substr('xyzzy', 1, 
> length(link)));
> 0
> sqlite>
> 

Thanks a lot.

> You'll need to write the Tcl code to do that with your variable $x where 
>   I've got 'defend' etc ...

I'll do.

> all I know about Tcl is that I don't want to 
> know any more about Tcl :-)

You should want to ;-)

> 
> HTH,
> John
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Thanks again

Ulli
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Check for existence of substring in table

[John Machin]

On 15/02/2009 8:17 PM, Ulrich Schöbel wrote:
> Hi all,
> 
> I'm stuck with my problem. Hopefully someone here can help.
> 
> I have a very simple table 'friends' with only one column
> 'link':
> 
> create table friends (link text);
> 
> Lets assume there are 2 rows, 'abc' and 'def'.
> 
> Then there is a Tcl variable x containing a string. If $x
> starts with either abc or def (if $x starts with any value
> in the table) I want a TRUE value (or something
> comparable) otherwise a FALSE.

You haven't said whether you problem is writing the SQL or the Tcl or 
both ... here's some SQL that will return 1 if any such link value 
exists, else 0. Are you sure you don't want to know which link value 
matches? What happens if more than 1 link value matches -- do you care?


sqlite> create table f (link text);
sqlite> insert into f values('abc');
sqlite> insert into f values('def');
sqlite> select * from f;
abc
def
sqlite> select exists(select 1 from f where link = substr('defend', 1, 
length(link)));
1
sqlite> select exists(select 1 from f where link = substr('abcpqr', 1, 
length(link)));
1
sqlite> select exists(select 1 from f where link = substr('xyzzy', 1, 
length(link)));
0
sqlite>

You'll need to write the Tcl code to do that with your variable $x where 
  I've got 'defend' etc ... all I know about Tcl is that I don't want to 
know any more about Tcl :-)

HTH,
John
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Check for existence of substring in table

[Ulrich Schöbel]

Hi all,

I'm stuck with my problem. Hopefully someone here can help.

I have a very simple table 'friends' with only one column
'link':

create table friends (link text);

Lets assume there are 2 rows, 'abc' and 'def'.

Then there is a Tcl variable x containing a string. If $x
starts with either abc or def (if $x starts with any value
in the table) I want a TRUE value (or something
comparable) otherwise a FALSE.

How can I do that?

Thanks for your time and help.

Ulli
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users