Re: [sqlite] instr function or equivalent
Am 10.09.2012 17:17, schrieb Bart Smissaert: Ah, OK. I have a feeling that needs to be done either in your application code or with a user defined SQLite function. Somebody may prove me wrong. Hi Bart, since I know you're using my COM-Wrapper, a larger set of Functions (in VBA-Style, similar to the JET-Engine) are already built-in there (including Instr, Left$, Right$, Mid$, DateDiff, DatePart, ... etc.). For example Sébastiens requirement could be handled this way: Select Mid$(TheField, Instr(TheField,'[') From Tbl Olaf BTW, a new version including newest SQLite 3.7.14 is out now since yesterday: www.datenhaus.de/Downloads/vbRC4BaseDlls.zip ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] instr function or equivalent
Hi Olaf, Yes, I am aware of those functions in your COM wrapper and I am using them. Was just thinking in general terms as not many users on this forum use VB. Great news about the new version and will download and test that today. Thanks for that. Bart On 9/11/12, Olaf Schmidt s...@online.de wrote: Am 10.09.2012 17:17, schrieb Bart Smissaert: Ah, OK. I have a feeling that needs to be done either in your application code or with a user defined SQLite function. Somebody may prove me wrong. Hi Bart, since I know you're using my COM-Wrapper, a larger set of Functions (in VBA-Style, similar to the JET-Engine) are already built-in there (including Instr, Left$, Right$, Mid$, DateDiff, DatePart, ... etc.). For example Sébastiens requirement could be handled this way: Select Mid$(TheField, Instr(TheField,'[') From Tbl Olaf BTW, a new version including newest SQLite 3.7.14 is out now since yesterday: www.datenhaus.de/Downloads/vbRC4BaseDlls.zip ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] instr function or equivalent
Hi Olaf, I think it should be: Select Left$(TheField, Instr(TheField,']')) From Tbl Have tested you new dll's and all working fine as usual. Bart On 9/11/12, Olaf Schmidt s...@online.de wrote: Am 10.09.2012 17:17, schrieb Bart Smissaert: Ah, OK. I have a feeling that needs to be done either in your application code or with a user defined SQLite function. Somebody may prove me wrong. Hi Bart, since I know you're using my COM-Wrapper, a larger set of Functions (in VBA-Style, similar to the JET-Engine) are already built-in there (including Instr, Left$, Right$, Mid$, DateDiff, DatePart, ... etc.). For example Sébastiens requirement could be handled this way: Select Mid$(TheField, Instr(TheField,'[') From Tbl Olaf BTW, a new version including newest SQLite 3.7.14 is out now since yesterday: www.datenhaus.de/Downloads/vbRC4BaseDlls.zip ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] instr function or equivalent
Hi, I'm looking for the[in]famous sqlite *instr* function which doesn't exist (searched the web so far without success). Also searched for a *position*function, without success too! Any idea or help? I found some threads about custom functions but no tutorial nor deeper explanations! Many thanks. Sébastien Roux ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] instr function or equivalent
http://www.sqlite.org/lang_corefunc.html Look at substr RBS On 9/10/12, Sébastien Roux roux.sebast...@gmail.com wrote: Hi, I'm looking for the[in]famous sqlite *instr* function which doesn't exist (searched the web so far without success). Also searched for a *position*function, without success too! Any idea or help? I found some threads about custom functions but no tutorial nor deeper explanations! Many thanks. Sébastien Roux ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] instr function or equivalent
Thanks Bart but substr require hard coded positions I guess, I need to get this position dynamically! Sébastien Roux 2012/9/10 Bart Smissaert bart.smissa...@gmail.com http://www.sqlite.org/lang_corefunc.html Look at substr RBS On 9/10/12, Sébastien Roux roux.sebast...@gmail.com wrote: Hi, I'm looking for the[in]famous sqlite *instr* function which doesn't exist (searched the web so far without success). Also searched for a *position*function, without success too! Any idea or help? I found some threads about custom functions but no tutorial nor deeper explanations! Many thanks. Sébastien Roux ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] instr function or equivalent
Could your application supply the values in code? What exactly are you trying to do? RBS On 9/10/12, Sébastien Roux roux.sebast...@gmail.com wrote: Thanks Bart but substr require hard coded positions I guess, I need to get this position dynamically! Sébastien Roux 2012/9/10 Bart Smissaert bart.smissa...@gmail.com http://www.sqlite.org/lang_corefunc.html Look at substr RBS On 9/10/12, Sébastien Roux roux.sebast...@gmail.com wrote: Hi, I'm looking for the[in]famous sqlite *instr* function which doesn't exist (searched the web so far without success). Also searched for a *position*function, without success too! Any idea or help? I found some threads about custom functions but no tutorial nor deeper explanations! Many thanks. Sébastien Roux ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] instr function or equivalent
I want to remove/trim characters strating from from til ] Logging in user [aa] from [10.165.69.247] 194|2012-09-07|Logging in user [a] from [10.296.44.163] 160|2012-09-04|Logging in user [aaa] from [10.164.69.248] 136|2012-09-07|Logging in user [aaa] from [10.168.59.169] 132|2012-09-07|Logging in user [aaa] from [10.169.22.58] Sébastien Roux 2012/9/10 Bart Smissaert bart.smissa...@gmail.com Could your application supply the values in code? What exactly are you trying to do? RBS On 9/10/12, Sébastien Roux roux.sebast...@gmail.com wrote: Thanks Bart but substr require hard coded positions I guess, I need to get this position dynamically! Sébastien Roux 2012/9/10 Bart Smissaert bart.smissa...@gmail.com http://www.sqlite.org/lang_corefunc.html Look at substr RBS On 9/10/12, Sébastien Roux roux.sebast...@gmail.com wrote: Hi, I'm looking for the[in]famous sqlite *instr* function which doesn't exist (searched the web so far without success). Also searched for a *position*function, without success too! Any idea or help? I found some threads about custom functions but no tutorial nor deeper explanations! Many thanks. Sébastien Roux ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] instr function or equivalent
Ah, OK. I have a feeling that needs to be done either in your application code or with a user defined SQLite function. Somebody may prove me wrong. RBS On 9/10/12, Sébastien Roux roux.sebast...@gmail.com wrote: I want to remove/trim characters strating from from til ] Logging in user [aa] from [10.165.69.247] 194|2012-09-07|Logging in user [a] from [10.296.44.163] 160|2012-09-04|Logging in user [aaa] from [10.164.69.248] 136|2012-09-07|Logging in user [aaa] from [10.168.59.169] 132|2012-09-07|Logging in user [aaa] from [10.169.22.58] Sébastien Roux 2012/9/10 Bart Smissaert bart.smissa...@gmail.com Could your application supply the values in code? What exactly are you trying to do? RBS On 9/10/12, Sébastien Roux roux.sebast...@gmail.com wrote: Thanks Bart but substr require hard coded positions I guess, I need to get this position dynamically! Sébastien Roux 2012/9/10 Bart Smissaert bart.smissa...@gmail.com http://www.sqlite.org/lang_corefunc.html Look at substr RBS On 9/10/12, Sébastien Roux roux.sebast...@gmail.com wrote: Hi, I'm looking for the[in]famous sqlite *instr* function which doesn't exist (searched the web so far without success). Also searched for a *position*function, without success too! Any idea or help? I found some threads about custom functions but no tutorial nor deeper explanations! Many thanks. Sébastien Roux ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] instr function or equivalent
Sad! Would you have any link toward SQLite's user defined SQLite function? Many thanks. Sébastien Roux 2012/9/10 Bart Smissaert bart.smissa...@gmail.com Ah, OK. I have a feeling that needs to be done either in your application code or with a user defined SQLite function. Somebody may prove me wrong. RBS On 9/10/12, Sébastien Roux roux.sebast...@gmail.com wrote: I want to remove/trim characters strating from from til ] Logging in user [aa] from [10.165.69.247] 194|2012-09-07|Logging in user [a] from [10.296.44.163] 160|2012-09-04|Logging in user [aaa] from [10.164.69.248] 136|2012-09-07|Logging in user [aaa] from [10.168.59.169] 132|2012-09-07|Logging in user [aaa] from [10.169.22.58] Sébastien Roux 2012/9/10 Bart Smissaert bart.smissa...@gmail.com Could your application supply the values in code? What exactly are you trying to do? RBS On 9/10/12, Sébastien Roux roux.sebast...@gmail.com wrote: Thanks Bart but substr require hard coded positions I guess, I need to get this position dynamically! Sébastien Roux 2012/9/10 Bart Smissaert bart.smissa...@gmail.com http://www.sqlite.org/lang_corefunc.html Look at substr RBS On 9/10/12, Sébastien Roux roux.sebast...@gmail.com wrote: Hi, I'm looking for the[in]famous sqlite *instr* function which doesn't exist (searched the web so far without success). Also searched for a *position*function, without success too! Any idea or help? I found some threads about custom functions but no tutorial nor deeper explanations! Many thanks. Sébastien Roux ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] instr function or equivalent
Does this work for you?
SQLite version 3.7.13 2012-06-11 02:05:22
Enter .help for instructions
Enter SQL statements terminated with a ;
sqlite create table t(s);
sqlite insert into t values('Logging in user [aa] from
[10.165.69.247]');
sqlite insert into t values('194|2012-09-07|Logging in user [a] from
[10.296.44.163]');
sqlite insert into t values('160|2012-09-04|Logging in user [aaa] from
[10.164.69.248]');
sqlite insert into t values('136|2012-09-07|Logging in user [aaa] from
[10.168.59.169]');
sqlite insert into t values('132|2012-09-07|Logging in user [aaa] from
[10.169.22.58]');
sqlite select rtrim(rtrim(s,']'),'.1234567890[ from') from t;
Logging in user [aa]
194|2012-09-07|Logging in user [a]
160|2012-09-04|Logging in user [aaa]
136|2012-09-07|Logging in user [aaa]
132|2012-09-07|Logging in user [aaa]
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Sébastien Roux [roux.sebast...@gmail.com]
Sent: Monday, September 10, 2012 10:22 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] instr function or equivalent
Sad! Would you have any link toward SQLite's user defined SQLite function?
Many thanks.
Sébastien Roux
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] instr function or equivalent
Enormous! Thanks it works!
Sébastien Roux
2012/9/10 Black, Michael (IS) michael.bla...@ngc.com
Does this work for you?
SQLite version 3.7.13 2012-06-11 02:05:22
Enter .help for instructions
Enter SQL statements terminated with a ;
sqlite create table t(s);
sqlite insert into t values('Logging in user [aa] from
[10.165.69.247]');
sqlite insert into t values('194|2012-09-07|Logging in user [a] from
[10.296.44.163]');
sqlite insert into t values('160|2012-09-04|Logging in user [aaa]
from [10.164.69.248]');
sqlite insert into t values('136|2012-09-07|Logging in user [aaa] from
[10.168.59.169]');
sqlite insert into t values('132|2012-09-07|Logging in user [aaa]
from [10.169.22.58]');
sqlite select rtrim(rtrim(s,']'),'.1234567890[ from') from t;
Logging in user [aa]
194|2012-09-07|Logging in user [a]
160|2012-09-04|Logging in user [aaa]
136|2012-09-07|Logging in user [aaa]
132|2012-09-07|Logging in user [aaa]
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
on behalf of Sébastien Roux [roux.sebast...@gmail.com]
Sent: Monday, September 10, 2012 10:22 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] instr function or equivalent
Sad! Would you have any link toward SQLite's user defined SQLite function?
Many thanks.
Sébastien Roux
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] instr function or equivalent
Nice one! Works here.
RBS
On 9/10/12, Black, Michael (IS) michael.bla...@ngc.com wrote:
Does this work for you?
SQLite version 3.7.13 2012-06-11 02:05:22
Enter .help for instructions
Enter SQL statements terminated with a ;
sqlite create table t(s);
sqlite insert into t values('Logging in user [aa] from
[10.165.69.247]');
sqlite insert into t values('194|2012-09-07|Logging in user [a] from
[10.296.44.163]');
sqlite insert into t values('160|2012-09-04|Logging in user [aaa] from
[10.164.69.248]');
sqlite insert into t values('136|2012-09-07|Logging in user [aaa] from
[10.168.59.169]');
sqlite insert into t values('132|2012-09-07|Logging in user [aaa]
from [10.169.22.58]');
sqlite select rtrim(rtrim(s,']'),'.1234567890[ from') from t;
Logging in user [aa]
194|2012-09-07|Logging in user [a]
160|2012-09-04|Logging in user [aaa]
136|2012-09-07|Logging in user [aaa]
132|2012-09-07|Logging in user [aaa]
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Sébastien Roux [roux.sebast...@gmail.com]
Sent: Monday, September 10, 2012 10:22 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] instr function or equivalent
Sad! Would you have any link toward SQLite's user defined SQLite function?
Many thanks.
Sébastien Roux
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] instr function or equivalent
This is slightly faster:
select rtrim(s,' from [.0123456789]') || ']' from t
RBS
On 9/10/12, Bart Smissaert bart.smissa...@gmail.com wrote:
Nice one! Works here.
RBS
On 9/10/12, Black, Michael (IS) michael.bla...@ngc.com wrote:
Does this work for you?
SQLite version 3.7.13 2012-06-11 02:05:22
Enter .help for instructions
Enter SQL statements terminated with a ;
sqlite create table t(s);
sqlite insert into t values('Logging in user [aa] from
[10.165.69.247]');
sqlite insert into t values('194|2012-09-07|Logging in user [a] from
[10.296.44.163]');
sqlite insert into t values('160|2012-09-04|Logging in user [aaa]
from
[10.164.69.248]');
sqlite insert into t values('136|2012-09-07|Logging in user [aaa] from
[10.168.59.169]');
sqlite insert into t values('132|2012-09-07|Logging in user
[aaa]
from [10.169.22.58]');
sqlite select rtrim(rtrim(s,']'),'.1234567890[ from') from t;
Logging in user [aa]
194|2012-09-07|Logging in user [a]
160|2012-09-04|Logging in user [aaa]
136|2012-09-07|Logging in user [aaa]
132|2012-09-07|Logging in user [aaa]
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
on
behalf of Sébastien Roux [roux.sebast...@gmail.com]
Sent: Monday, September 10, 2012 10:22 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] instr function or equivalent
Sad! Would you have any link toward SQLite's user defined SQLite
function?
Many thanks.
Sébastien Roux
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] instr function or equivalent
It might be faster but it doesn't work for anybody who has any letters in
from in their name.
sqlite insert into t values('132|2012-09-07|Logging in user [tom] from
[10.169.22.59]');
sqlite select rtrim(s,' from [.0123456789]') || ']' from t;
Logging in user [aa]
194|2012-09-07|Logging in user [a]
160|2012-09-04|Logging in user [aaa]
136|2012-09-07|Logging in user [aaa]
132|2012-09-07|Logging in user [aaa]
132|2012-09-07|Logging in user [t]
The original way still works just fine.
sqlite select rtrim(rtrim(s,']'),'.1234567890[ from') from t;
Logging in user [aa]
194|2012-09-07|Logging in user [a]
160|2012-09-04|Logging in user [aaa]
136|2012-09-07|Logging in user [aaa]
132|2012-09-07|Logging in user [aaa]
132|2012-09-07|Logging in user [tom]
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
You have to be very careful when parsing char sets like this to ensure your
barriers are valid.
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Bart Smissaert [bart.smissa...@gmail.com]
Sent: Monday, September 10, 2012 11:19 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] instr function or equivalent
This is slightly faster:
select rtrim(s,' from [.0123456789]') || ']' from t
RBS
On 9/10/12, Bart Smissaert bart.smissa...@gmail.com wrote:
Nice one! Works here.
RBS
On 9/10/12, Black, Michael (IS) michael.bla...@ngc.com wrote:
Does this work for you?
SQLite version 3.7.13 2012-06-11 02:05:22
Enter .help for instructions
Enter SQL statements terminated with a ;
sqlite create table t(s);
sqlite insert into t values('Logging in user [aa] from
[10.165.69.247]');
sqlite insert into t values('194|2012-09-07|Logging in user [a] from
[10.296.44.163]');
sqlite insert into t values('160|2012-09-04|Logging in user [aaa]
from
[10.164.69.248]');
sqlite insert into t values('136|2012-09-07|Logging in user [aaa] from
[10.168.59.169]');
sqlite insert into t values('132|2012-09-07|Logging in user
[aaa]
from [10.169.22.58]');
sqlite select rtrim(rtrim(s,']'),'.1234567890[ from') from t;
Logging in user [aa]
194|2012-09-07|Logging in user [a]
160|2012-09-04|Logging in user [aaa]
136|2012-09-07|Logging in user [aaa]
132|2012-09-07|Logging in user [aaa]
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
on
behalf of Sébastien Roux [roux.sebast...@gmail.com]
Sent: Monday, September 10, 2012 10:22 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] instr function or equivalent
Sad! Would you have any link toward SQLite's user defined SQLite
function?
Many thanks.
Sébastien Roux
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] instr function or equivalent
Yes, you are right there.
As rtrim incorporates an instr type of function I am not sure why there
is no plain instr function in SQLite. It would make things a bit simpler.
RBS
On 9/10/12, Black, Michael (IS) michael.bla...@ngc.com wrote:
It might be faster but it doesn't work for anybody who has any letters in
from in their name.
sqlite insert into t values('132|2012-09-07|Logging in user [tom] from
[10.169.22.59]');
sqlite select rtrim(s,' from [.0123456789]') || ']' from t;
Logging in user [aa]
194|2012-09-07|Logging in user [a]
160|2012-09-04|Logging in user [aaa]
136|2012-09-07|Logging in user [aaa]
132|2012-09-07|Logging in user [aaa]
132|2012-09-07|Logging in user [t]
The original way still works just fine.
sqlite select rtrim(rtrim(s,']'),'.1234567890[ from') from t;
Logging in user [aa]
194|2012-09-07|Logging in user [a]
160|2012-09-04|Logging in user [aaa]
136|2012-09-07|Logging in user [aaa]
132|2012-09-07|Logging in user [aaa]
132|2012-09-07|Logging in user [tom]
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
You have to be very careful when parsing char sets like this to ensure your
barriers are valid.
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Bart Smissaert [bart.smissa...@gmail.com]
Sent: Monday, September 10, 2012 11:19 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] instr function or equivalent
This is slightly faster:
select rtrim(s,' from [.0123456789]') || ']' from t
RBS
On 9/10/12, Bart Smissaert bart.smissa...@gmail.com wrote:
Nice one! Works here.
RBS
On 9/10/12, Black, Michael (IS) michael.bla...@ngc.com wrote:
Does this work for you?
SQLite version 3.7.13 2012-06-11 02:05:22
Enter .help for instructions
Enter SQL statements terminated with a ;
sqlite create table t(s);
sqlite insert into t values('Logging in user [aa] from
[10.165.69.247]');
sqlite insert into t values('194|2012-09-07|Logging in user [a]
from
[10.296.44.163]');
sqlite insert into t values('160|2012-09-04|Logging in user [aaa]
from
[10.164.69.248]');
sqlite insert into t values('136|2012-09-07|Logging in user [aaa] from
[10.168.59.169]');
sqlite insert into t values('132|2012-09-07|Logging in user
[aaa]
from [10.169.22.58]');
sqlite select rtrim(rtrim(s,']'),'.1234567890[ from') from t;
Logging in user [aa]
194|2012-09-07|Logging in user [a]
160|2012-09-04|Logging in user [aaa]
136|2012-09-07|Logging in user [aaa]
132|2012-09-07|Logging in user [aaa]
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
on
behalf of Sébastien Roux [roux.sebast...@gmail.com]
Sent: Monday, September 10, 2012 10:22 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] instr function or equivalent
Sad! Would you have any link toward SQLite's user defined SQLite
function?
Many thanks.
Sébastien Roux
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] instr function or equivalent
On 10 Sep 2012, at 5:36pm, Bart Smissaert bart.smissa...@gmail.com wrote: Yes, you are right there. As rtrim incorporates an instr type of function I am not sure why there is no plain instr function in SQLite. It would make things a bit simpler. Two ways to add appropriate functions to SQLite to do it. Either supply a 'find substring' function which returns the character number of where the substring is found, or supply a GLOB-type or regexp-type 'replace' function. Neither of them are trivial given that strings /may/ be 16-bit. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] instr function or equivalent
Yes, but doesn't rtrim do an instr function with the same problems as you mention? RBS On 9/10/12, Simon Slavin slav...@bigfraud.org wrote: On 10 Sep 2012, at 5:36pm, Bart Smissaert bart.smissa...@gmail.com wrote: Yes, you are right there. As rtrim incorporates an instr type of function I am not sure why there is no plain instr function in SQLite. It would make things a bit simpler. Two ways to add appropriate functions to SQLite to do it. Either supply a 'find substring' function which returns the character number of where the substring is found, or supply a GLOB-type or regexp-type 'replace' function. Neither of them are trivial given that strings /may/ be 16-bit. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] instr function or equivalent
On 10 Sep 2012, at 5:43pm, Bart Smissaert bart.smissa...@gmail.com wrote: Yes, but doesn't rtrim do an instr function with the same problems as you mention? Similar but it only every has to (recursively) look at the rightmost character. 'instr' has to be able to look at substrings all the way along the string. I'm not saying it's impossible, merely that it isn't a trivial three line C function. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] instr function or equivalent
Hi, You could check the sqlite extensions from www.monkeybreadsoftware.de at: http://www.monkeybreadsoftware.de/SQLiteExtension/functions.shtml Best Regards, Carlos. Em 10/09/2012 10:21, Sébastien Roux escreveu: Hi, I'm looking for the[in]famous sqlite *instr* function which doesn't exist (searched the web so far without success). Also searched for a *position*function, without success too! Any idea or help? I found some threads about custom functions but no tutorial nor deeper explanations! Many thanks. Sébastien Roux ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] instr function or equivalent
On 9/10/2012 12:45 PM, Simon Slavin wrote: Similar but it only every has to (recursively) look at the rightmost character. 'instr' has to be able to look at substrings all the way along the string. So does replace(), which SQLite does implement. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] instr function or equivalent
Here's one that I wrote a while back:
void sqlite3_instr(sqlite3_context* pContext, int argc, sqlite3_value** argv)
{
const char *str1 = (const char *) sqlite3_value_text(argv[0]);
const char *str2 = (const char *) sqlite3_value_text(argv[1]);
char *p = strstr(str1, str2);
int nResult = 0;
if(p != NULL)
{
nResult = p - str1 + 1;
}
sqlite3_result_int(pContext, nResult);
}
You'll need to register it as an extension function on the database connection,
but it should do the job.
Kenneth Ballard
On September 10, 2012 at 9:21 AM Sébastien Roux roux.sebast...@gmail.com
wrote:
Hi,
I'm looking for the[in]famous sqlite *instr* function which doesn't exist
(searched the web so far without success). Also searched for a
*position*function, without success too!
Any idea or help? I found some threads about custom functions but no
tutorial nor deeper explanations!
Many thanks.
Sébastien Roux
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] instr function or equivalent
On Sep 10, 2012, at 4:21 PM, Sébastien Roux roux.sebast...@gmail.com wrote: I'm looking for the[in]famous sqlite *instr* function which doesn't exist (searched the web so far without success). Also searched for a *position*function, without success too! Any idea or help? I found some threads about custom functions but no tutorial nor deeper explanations! (not sure if anyone mentioned it already, but…) Take a look at Liam Healy's extension-functions.c: http://www.sqlite.org/contrib/download/extension-functions.c?get=25 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] instr function or equivalent
On 9/10/2012 1:22 PM,
kball...@kennethballard.com wrote:
Here's one that I wrote a while back:
void sqlite3_instr(sqlite3_context* pContext, int argc, sqlite3_value** argv)
{
const char *str1 = (const char *) sqlite3_value_text(argv[0]);
const char *str2 = (const char *) sqlite3_value_text(argv[1]);
char *p = strstr(str1, str2);
int nResult = 0;
if(p != NULL)
{
nResult = p - str1 + 1;
}
sqlite3_result_int(pContext, nResult);
}
This returns an offset in bytes, not in characters. I would expect a
reasonable instr() implementation to satisfy this identity:
substr(X, instr(X, Y), length(Y)) == Y
for all strings X and Y, or at least for those where Y does in fact
appear in X. I don't think yours would satisfy this condition for
strings containing non-ASCII characters.
One would probably want to study the implementation of substr and length
before implementing instr, to make sure the three play well together.
--
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
