Re: [sqlite] Store and retreive 0D0A (CRLF) in string field
SQLite doesn't truncate anything. Whatever you put in you get out. If you see a truncation, it is either done by whatever wrapper you use on top of SQLite, or simply an artifact of the way you inspect the data (e.g. you look at the string in a debugger, and the debugger just happens to show only the first line). Use sqlite3_column_bytes[16] to convince yourself that SQLite gives you the complete string. Igor Tandetnik Thanks for that rather blunt reply. I had already figured out the data was there by using a professional sqlite tool. Now I just have to figure out how my compiler is handling the returned data. -- View this message in context: http://www.nabble.com/Store-and-retreive-0D0A-%28CRLF%29-in-string-field-tf3461423.html#a9666968 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Store and retreive 0D0A (CRLF) in string field
When I have text pasted into an sqlite string field, it is stored okay but when I retrieve a string, it is truncated at the first CR (0D). Does anyone know how to handle this by any chance? -- View this message in context: http://www.nabble.com/Store-and-retreive-0D0A-%28CRLF%29-in-string-field-tf3461423.html#a9657638 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: >> 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
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] -
[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] -
Re: [sqlite] compare open table and attached database table
Igor Tandetnik wrote: > > fangles <[EMAIL PROTECTED]> wrote: >> Hello, I am trying to compare a currently opened database table with >> a table from an attached database. Both tables have identical >> structures but the attached table has an extra record. >> >> The first lists records from the internal table NOT CONTAINED IN the >> attached table >> The second lists records NOT CONTAINED IN the internal table >> >> Select a.displayas AS displayas FROM addresses a INNER JOIN >> RemoteDb.addresses b ON a.displayas <> b.displayas > > This query doesn't do what you think it does. Once you get past the > little syntax problem, you'll get displayas from every record in > addresses table, each repeated multiple times. > > Make it > > select displayas from main.addresses > where displayas not in (select displayas from RemoteDb.addresses) > > Igor Tandetnik > > Thank you Igor, that's fantastic. I'm reading lots of SqLite tutorials but a lot of the SQL is so far out of my brain's reach that it doesn't make sense to me. And I love to play:):):) Have a great day. -- View this message in context: http://www.nabble.com/compare-open-table-and-attached-database-table-tf3250700.html#a9037663 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] compare open table and attached database table
Hello, I am trying to compare a currently opened database table with a table from an attached database. Both tables have identical structures but the attached table has an extra record. I wanted to do two queries. The first lists records from the internal table NOT CONTAINED IN the attached table The second lists records NOT CONTAINED IN the internal table I am using a field called "displayas" as the comparison for record existence and the attached database was called RemoteDb. The query below is just what I am playing with and predictably doesn't work and I am getting an ambiguous column reference error for "a.displayas". Could anyone help me please? I am fairly new at this. Select a.displayas AS displayas FROM addresses a INNER JOIN RemoteDb.addresses b ON a.displayas <> b.displayas -- View this message in context: http://www.nabble.com/compare-open-table-and-attached-database-table-tf3250700.html#a9036602 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
