Re: [sqlite] Search all collumns with LIKE at once
Martin Jenkins-2 wrote: > > fangles wrote: >> PK? I'm sorry but I am not familiar with that term.. > > Sorry, in this example a Primary Key is a column (eg an integer) which > uniquely specifies a row in a table. > > In the example above you: > > select all columns from the rows which have a PK in the set > ( > select the PK from all rows where the columns joined together > matches your search text > ) > > BTW, I don't know how well this performs compared to the FTS extensions. > > Martin > > Thanks Martin, the acronym threw me originally LOL. I'd have to redesign the original database to do FTS so your way using PK seems easier for now. -- View this message in context: http://www.nabble.com/Search-all-collumns-with-LIKE-at-once-tf3251161.html#a9040441 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Search all collumns with LIKE at once
fangles wrote: PK? I'm sorry but I am not familiar with that term.. Sorry, in this example a Primary Key is a column (eg an integer) which uniquely specifies a row in a table. In the example above you: select all columns from the rows which have a PK in the set ( select the PK from all rows where the columns joined together matches your search text ) BTW, I don't know how well this performs compared to the FTS extensions. Martin - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Search all collumns with LIKE at once
Martin Jenkins-2 wrote: > > fangles wrote: >> I'm currently searching through all columns in a table to see if any >> match >> the search text and the query is rather cumbersome. Is there a way to use >> a >> loop to go through all available columns by some means? Maybe a loop by >> querying the schema? >> >> SELECT * FROM addresses WHERE title LIKE '% + searchtext + %' >> OR first LIKE '% + searchtext + "%' >> OR middle LIKE '% + searchtext + "%' >> OR last LIKE '% + searchtext + "%' >> OR street LIKE '% + searchtext + "%' >> OR suburb LIKE '% + searchtext + "%' >> OR city LIKE '% + searchtext + "%' >> OR postcode LIKE '% + searchtext + "%' >> OR state LIKE '% + searchtext + "%' >> OR country LIKE '%+ searchtext + "%' >> OR work LIKE '% + searchtext + "%' >> OR home LIKE '% + searchtext + "%' >> OR mobile LIKE '% + searchtext + "%' >> OR company LIKE '%+ searchtext + "%' >> OR misc1 LIKE '% + searchtext + "%' >> OR misc2 LIKE '% + searchtext + "%' >> OR email LIKE '% + searchtext + "%' >> OR note LIKE '% + searchtext + "%' >> OR category LIKE '% + searchtext + "%' >> OR displayas LIKE '% + searchtext + "%' >> OR firstentry LIKE '% + searchtext + "%' >> OR lastedit LIKE '% + searchtext + "%' >> OR deleted LIKE '%+ searchtext + "%' ORDER BY displayas >> >> This is a big one!! > > Could you not join all the columns and search that? > > select * from addresses where first||middle...deleted like searchtext; > > Martin > > Probably because I didn't know about it. Hmm, so much to learn, so little brain -- View this message in context: http://www.nabble.com/Search-all-collumns-with-LIKE-at-once-tf3251161.html#a9039025 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Search all collumns with LIKE at once
Martin Jenkins-2 wrote: > > fangles wrote: >> I'm currently searching through all columns in a table to see if any >> match >> the search text and the query is rather cumbersome. Is there a way to use >> a >> loop to go through all available columns by some means? Maybe a loop by >> querying the schema? > > If you had a PK on that table and used a view to concatenate the columns > > create view v as select pk, first||...||deleted as cols from addr; > > you could reduce the select to > > select * from addr where pk = (select pk from v where cols like srch); > > Martin > > PK? I'm sorry but I am not familiar with that term.. -- View this message in context: http://www.nabble.com/Search-all-collumns-with-LIKE-at-once-tf3251161.html#a9038981 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Search all collumns with LIKE at once
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 fangles wrote: > I'm currently searching through all columns in a table to see if any match > the search text and the query is rather cumbersome. Have you considered http://www.sqlite.org/cvstrac/wiki?p=FtsTwo Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFF2VL+mOOfHg372QQRArtdAKDUap+FIYHN/z7j7c3MkPGRhAd/WwCff9yr dwzO5IkKuh2VpQREyq7oJO8= =YpKB -END PGP SIGNATURE- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Search all collumns with LIKE at once
fangles wrote: I'm currently searching through all columns in a table to see if any match the search text and the query is rather cumbersome. Is there a way to use a loop to go through all available columns by some means? Maybe a loop by querying the schema? If you had a PK on that table and used a view to concatenate the columns create view v as select pk, first||...||deleted as cols from addr; you could reduce the select to select * from addr where pk = (select pk from v where cols like srch); Martin - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Search all collumns with LIKE at once
fangles wrote: I'm currently searching through all columns in a table to see if any match the search text and the query is rather cumbersome. Is there a way to use a loop to go through all available columns by some means? Maybe a loop by querying the schema? SELECT * FROM addresses WHERE title LIKE '% + searchtext + %' OR first LIKE '% + searchtext + "%' OR middle LIKE '% + searchtext + "%' OR last LIKE '% + searchtext + "%' OR street LIKE '% + searchtext + "%' OR suburb LIKE '% + searchtext + "%' OR city LIKE '% + searchtext + "%' OR postcode LIKE '% + searchtext + "%' OR state LIKE '% + searchtext + "%' OR country LIKE '%+ searchtext + "%' OR work LIKE '% + searchtext + "%' OR home LIKE '% + searchtext + "%' OR mobile LIKE '% + searchtext + "%' OR company LIKE '%+ searchtext + "%' OR misc1 LIKE '% + searchtext + "%' OR misc2 LIKE '% + searchtext + "%' OR email LIKE '% + searchtext + "%' OR note LIKE '% + searchtext + "%' OR category LIKE '% + searchtext + "%' OR displayas LIKE '% + searchtext + "%' OR firstentry LIKE '% + searchtext + "%' OR lastedit LIKE '% + searchtext + "%' OR deleted LIKE '%+ searchtext + "%' ORDER BY displayas This is a big one!! Could you not join all the columns and search that? select * from addresses where first||middle...deleted like searchtext; Martin - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Search all collumns with LIKE at once
I'm currently searching through all columns in a table to see if any match the search text and the query is rather cumbersome. Is there a way to use a loop to go through all available columns by some means? Maybe a loop by querying the schema? SELECT * FROM addresses WHERE title LIKE '% + searchtext + %' OR first LIKE '% + searchtext + "%' OR middle LIKE '% + searchtext + "%' OR last LIKE '% + searchtext + "%' OR street LIKE '% + searchtext + "%' OR suburb LIKE '% + searchtext + "%' OR city LIKE '% + searchtext + "%' OR postcode LIKE '% + searchtext + "%' OR state LIKE '% + searchtext + "%' OR country LIKE '%+ searchtext + "%' OR work LIKE '% + searchtext + "%' OR home LIKE '% + searchtext + "%' OR mobile LIKE '% + searchtext + "%' OR company LIKE '%+ searchtext + "%' OR misc1 LIKE '% + searchtext + "%' OR misc2 LIKE '% + searchtext + "%' OR email LIKE '% + searchtext + "%' OR note LIKE '% + searchtext + "%' OR category LIKE '% + searchtext + "%' OR displayas LIKE '% + searchtext + "%' OR firstentry LIKE '% + searchtext + "%' OR lastedit LIKE '% + searchtext + "%' OR deleted LIKE '%+ searchtext + "%' ORDER BY displayas This is a big one!! -- View this message in context: http://www.nabble.com/Search-all-collumns-with-LIKE-at-once-tf3251161.html#a9037784 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
