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, '<a href=''/FAQ/', '<a 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. So, any thoughts, ideas or a good tutorial would be greatly appreciated. Thanks! -- View this message in context: 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