Re: [sqlite] sql function to change multiple links within a DB
sqlite> create table questions (answer text);
sqlite> insert into questions values('/FAQ/Doctors');
sqlite> select * from questions;
/FAQ/Doctors
sqlite> select
ltrim(ltrim(ltrim(answer,'/'),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'),'/')
from questions;
Doctors
You need to add whatever chars are valid for your first part of your link --
e.g. 01234567890 if appropriate.
The sequence is:
trim the first slash
trim the chars
trim the 2nd slash
Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of teahou [tea...@gmail.com]
Sent: Wednesday, August 31, 2011 6:37 PM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] sql function to change multiple links within a DB
Hello. I am doing some work on a SQLite table with just over 5000 rows. One
column holds some html text with links within, and I need to change every
one of those links to a new format.
A sample link looks like this:
/FAQ/Doctors Doctors
We will call the 'FAQ' portion of the link, the category and the 'Doctors'
portion the Item.
I need to change it this:
Doctors
I would like change all links with one go, but I can't figure out a way to
'wildcard' the category and items. There are 60 different categories and
thousands of different items.
The closest I have come is to do it in parts, I came up with this:
UPDATE questions SET answer = REPLACE(answer, 'http://old.nabble.com/sql-function-to-change-multiple-links-within-a-DB-tp32376393p32376393.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] sql function to change multiple links within a DB
Simon Slavin-3 wrote:
>
>
> On 1 Sep 2011, at 12:37am, teahou wrote:
>
>> The closest I have come is to do it in parts, I came up with this:
>>
>> UPDATE questions SET answer = REPLACE(answer, ' style="text-decoration:
>> underline; color: blue"
>> onclick="Ti.App.fireEvent(''openContent'', {kind: ''FAQ'', find: "' );
>>
>> As you can see, this just does the first part, and forces me to do each
>> category individually.
>>
>> I had thought to use a combination of substr/instr but the link locations
>> vary, it could be at the beginning of the text, or it could start after
>> 100
>> characters.
>
> Do it in software. That's what programming languages are for. 10
> seconds to take a backup copy of the database. 30 minutes to write the
> program and test it, 10 seconds to run it.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
I don't know any. Well, i know some VB but i wouldn't even know where to
begin. I guess I will look into it unless there are some other replys
--
View this message in context:
http://old.nabble.com/sql-function-to-change-multiple-links-within-a-DB-tp32376393p32376446.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] sql function to change multiple links within a DB
On 1 Sep 2011, at 12:37am, teahou wrote:
> The closest I have come is to do it in parts, I came up with this:
>
> UPDATE questions SET answer = REPLACE(answer, ' style="text-decoration: underline; color: blue"
> onclick="Ti.App.fireEvent(''openContent'', {kind: ''FAQ'', find: "' );
>
> As you can see, this just does the first part, and forces me to do each
> category individually.
>
> I had thought to use a combination of substr/instr but the link locations
> vary, it could be at the beginning of the text, or it could start after 100
> characters.
Do it in software. That's what programming languages are for. 10 seconds to
take a backup copy of the database. 30 minutes to write the program and test
it, 10 seconds to run it.
Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sql function to change multiple links within a DB
Hello. I am doing some work on a SQLite table with just over 5000 rows. One column holds some html text with links within, and I need to change every one of those links to a new format. A sample link looks like this: /FAQ/Doctors Doctors We will call the 'FAQ' portion of the link, the category and the 'Doctors' portion the Item. I need to change it this: Doctors I would like change all links with one go, but I can't figure out a way to 'wildcard' the category and items. There are 60 different categories and thousands of different items. The closest I have come is to do it in parts, I came up with this: UPDATE questions SET answer = REPLACE(answer, 'http://old.nabble.com/sql-function-to-change-multiple-links-within-a-DB-tp32376393p32376393.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
