Re: [sqlite] EXTERNAL: SQLite query help pls
Spread the word...that's what these lists are for... Michael D. Black Senior Scientist Advanced Analytics Directorate Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org on behalf of Dickie.wild Sent: Tue 10/19/2010 8:03 AM To: sqlite-users@sqlite.org Subject: EXTERNAL:Re: [sqlite] EXTERNAL: SQLite query help pls Hi, Well i have to say i am like a kid in a sweet shop right now, you all may have just saved me 6 or so hours work. Thanks again for your input. I was wondering if anyone had any issues with me posting this up on the Boxee forum as there lots of people with this issue? I will of course tell people that it came from here. Thanks again, Rich -- View this message in context: http://old.nabble.com/SQLite-query-help-pls-tp29983175p2989.html Sent from the SQLite mailing list archive at Nabble.com. ___ 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] EXTERNAL: SQLite query help pls
Hi, Well i have to say i am like a kid in a sweet shop right now, you all may have just saved me 6 or so hours work. Thanks again for your input. I was wondering if anyone had any issues with me posting this up on the Boxee forum as there lots of people with this issue? I will of course tell people that it came from here. Thanks again, Rich -- View this message in context: http://old.nabble.com/SQLite-query-help-pls-tp29983175p2989.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] EXTERNAL: SQLite query help pls
Dickie.wild wrote: > I thought that looked like it would get the same results, but i seem to be > getting the following error, are you able to try it and let me know if your > getting the same error? > > SQLiteManager: Likely SQL syntax error: UPDATE video_files SET strCover TO > (rtrim(strPath,replace(strPath,'\','')) || 'folder.jpg') Make it UPDATE video_files SET strCover = rtrim(strPath,replace(strPath,'\','')) || 'folder.jpg'; -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] EXTERNAL: SQLite query help pls
Hi, I thought that looked like it would get the same results, but i seem to be getting the following error, are you able to try it and let me know if your getting the same error? SQLiteManager: Likely SQL syntax error: UPDATE video_files SET strCover TO (rtrim(strPath,replace(strPath,'\','')) || 'folder.jpg') [ near "TO": syntax error ] Exception Name: NS_ERROR_FAILURE Exception Message: Component returned failure code: 0x80004005 (NS_ERROR_FAILURE) [mozIStorageConnection.createStatement] -- View this message in context: http://old.nabble.com/SQLite-query-help-pls-tp29983175p2755.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] EXTERNAL: SQLite query help pls
Actually marbex came up with the best solutionwould work with ANY allowable
path characters.
UPDATE video_files SET strCover TO (rtrim(strPath,replace(strPath,'\','')) ||
'folder.jpg')
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org on behalf of Simon Slavin
Sent: Tue 10/19/2010 7:17 AM
To: General Discussion of SQLite Database
Subject: EXTERNAL:Re: [sqlite] EXTERNAL: SQLite query help pls
On 19 Oct 2010, at 1:07pm, Dickie.wild wrote:
> Thanks for the reply's i have tried the various ways described that they do
> not seem to be working. I have a way in which it works but this is in SQL
> could anyone convert it to SQLite for me? i am not to sure it is even
> possible. I have also attached the DB just encase anyone can do it for me?
>
> Update video_files
>
> Set strCover =
> Reverse(Substring(Reverse(strPath),CHARINDEX('\',Reverse(strPath)),1000)) +
> 'Folder.jpg'
We told you how to do it in SQLite. The answer involved using rtrim(). Two of
us even posted example code for you.
If you want an equivalent to that specific command then, copying from my post
of a few days ago, it would be something like
UPDATE video_files SET strCover TO (rtrim(strPath, 'abcd... ABCD... 123... ._')
|| 'folder.jpg')
(expanded to include alphabets and digits). If you have tried that and it
didn't work please tell us what it did instead of working.
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] EXTERNAL: SQLite query help pls
On 19 Oct 2010, at 1:07pm, Dickie.wild wrote:
> Thanks for the reply's i have tried the various ways described that they do
> not seem to be working. I have a way in which it works but this is in SQL
> could anyone convert it to SQLite for me? i am not to sure it is even
> possible. I have also attached the DB just encase anyone can do it for me?
>
> Update video_files
>
> Set strCover =
> Reverse(Substring(Reverse(strPath),CHARINDEX('\',Reverse(strPath)),1000)) +
> 'Folder.jpg'
We told you how to do it in SQLite. The answer involved using rtrim(). Two of
us even posted example code for you.
If you want an equivalent to that specific command then, copying from my post
of a few days ago, it would be something like
UPDATE video_files SET strCover TO (rtrim(strPath, 'abcd... ABCD... 123... ._')
|| 'folder.jpg')
(expanded to include alphabets and digits). If you have tried that and it
didn't work please tell us what it did instead of working.
Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] EXTERNAL: SQLite query help pls
Hi,
Thanks for the reply's i have tried the various ways described that they do
not seem to be working. I have a way in which it works but this is in SQL
could anyone convert it to SQLite for me? i am not to sure it is even
possible. I have also attached the DB just encase anyone can do it for me?
Update video_files
Set strCover =
Reverse(Substring(Reverse(strPath),CHARINDEX('\',Reverse(strPath)),1000)) +
'Folder.jpg'
http://old.nabble.com/file/p2497/Boxee_catalog.db Boxee_catalog.db
--
View this message in context:
http://old.nabble.com/SQLite-query-help-pls-tp29983175p2497.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] EXTERNAL: SQLite query help pls
Black, Michael (IS) wrote:
>
> Since there aren't a whole lot of string manipulaion functions (like
> indexof or such) try this:
>
> sqlite> create table t(s varchar);
> sqlite> create table t2(s varchar);
> sqlite> insert into t values('C:\richEminem\file.txt');
> sqlite> select rtrim(s,'._
> abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ')||'folder.jpg' from
> t;
> C:\richEminem\folder.jpg
> sqlite> insert into t2(s) select rtrim(s,'._
> abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ')||'folder.jpg' from
> t;
> sqlite> select * from t2;
> C:\richEminem\folder.jpg
>
> You do, of course, need to put all allowable characters in the rtrim
> character set (except the backslash or forward slash).
>
> Michael D. Black
> Senior Scientist
> Advanced Analytics Directorate
> Northrop Grumman Information Systems
>
>
I came up with this SQL some time ago when I needed to get the folder from a
full path, the beauty with it is that it always works regardless of which
characters you have in the filename. I though I'd share it.
select RTRIM(path,REPLACE(path,'\','')) from
(select 'C:\richEminem\file.txt' path)
--
View this message in context:
http://old.nabble.com/SQLite-query-help-pls-tp29983175p29985874.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] EXTERNAL: SQLite query help pls
Since there aren't a whole lot of string manipulaion functions (like indexof or
such) try this:
sqlite> create table t(s varchar);
sqlite> create table t2(s varchar);
sqlite> insert into t values('C:\richEminem\file.txt');
sqlite> select rtrim(s,'._
abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ')||'folder.jpg' from t;
C:\richEminem\folder.jpg
sqlite> insert into t2(s) select rtrim(s,'._
abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ')||'folder.jpg' from t;
sqlite> select * from t2;
C:\richEminem\folder.jpg
You do, of course, need to put all allowable characters in the rtrim character
set (except the backslash or forward slash).
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org on behalf of Dickie.wild
Sent: Sun 10/17/2010 7:26 AM
To: sqlite-users@sqlite.org
Subject: EXTERNAL:[sqlite] SQLite query help pls
Hi All,
I was hoping for a little help, well little would be an understatement, I
currently have a file location in a field and i would like to take all of it
up untill the last / (folder) and copy it in to another column and then
attach folder.jpg on the end. So the end result ends up being something like
c:\rich\Eminem\folder.jpg
I have never used SQLite before and help would be great
R
--
View this message in context:
http://old.nabble.com/SQLite-query-help-pls-tp29983175p29983175.html
Sent from the SQLite mailing list archive at Nabble.com.
___
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
