Re: [sqlite] How can I detect rows with non-ASCII values?

> > > GLOB supports character classes thanks for teaching new keyword and its use. My first attempt was very similar to what you suggest, except I used sqlite3 and re from inside Python. But as you see, I can't reliably seprate 'interrogative' question marks from question marks that get

Re: [sqlite] How can I detect rows with non-ASCII values?

wrote: > Rocky Ji wrote: > > I am asked to highlight rows containing strange characters. All data were > > ingested by a proprietary crawler. > > > > By strange, I mean, question marks, boxes, little Christmas Trees, solid > > arrows, etc. kind of symbols; these

[sqlite] How can I detect rows with non-ASCII values?

Hi, I am asked to highlight rows containing strange characters. All data were ingested by a proprietary crawler. By strange, I mean, question marks, boxes, little Christmas Trees, solid arrows, etc. kind of symbols; these appear suddenly in flow of normal ASCII English letters. How do I

[sqlite] Is there a tool to convert `where`s to equivalent `join`s?

In SQL world, generally , not just SQLite , a lot of pros say that the

Re: [sqlite] How to refer to `this` table?

I went from https://sqlite.org/lang_createtable.html to https://sqlite.org/syntax/table-constraint.html to https://sqlite.org/syntax/expr.html and figured expr of `check` in table constraint may contain a nested select after `not in`. On Sat, Feb 23, 2019, 1:24 PM Clemens Ladisch Rocky Ji

Re: [sqlite] What is the recommended way to write to views?

9, at 11:14am, Rocky Ji wrote: > > > create view summary > > as > >select > >a.a_attribute, > >b.b_attribute, > >c.c_attribute > >from > >m2mAlphaBeta m > >inner join Alpha a on a.a_id = m.FK_a_

Re: [sqlite] What is the recommended way to write to views?

t; insert into summary values ('C', 3.3, 'Three'); > > sqlite> select * from summary; > a_attribute|b_attribute|c_attribute > A|1.0|One > B|2.2|Two > C|3.3|Three > C|3.3|Three > > sqlite> > > > Also at the very end here I realize this also requires no triggers on > inse

[sqlite] How to refer to `this` table?

If I do CREATE TABLE Sample ( id INTEGER PRIMARY KEY AUTOINCREMENT, parent_id INTEGER, CONSTRAINT p FOREIGN KEY (parent_id) REFERENCES Sample (id) ); I don't get any errors and the schema behaves as expected. But if I try CREATE TABLE Aliases ( alias_id INTEGER PRIMARY KEY

[sqlite] What is the recommended way to write to views?

Hi everyone, I know the simple answer to be `instead of insert ...`. But consider a situation where: Alpha has-many Beta,and Beta has-many Alpha Alpha has-many Charlie, while Charlie has one Alpha So if my SQL looks like: -- PRAGMA fk ON; create table Alpha ( a_id integer primary key

Re: [sqlite] How to get aggregate without reducing number of rows (repeats are ok)?

id of them sub-query? Be nice. On Mon, Feb 18, 2019, 3:22 PM R Smith On 2019/02/18 11:24 AM, Clemens Ladisch wrote: > > Rocky Ji wrote: > >> But everyone advices against nested select statements. > > Who? > > > > I've heard rumors that older version

Re: [sqlite] How to get aggregate without reducing number of rows (repeats are ok)?

; the correlated subquery could be put right in the main query, and if the > database schema itself were normalized, then even further simplifications > would be possible). > > --- > The fact that there's a Highway to Hell but only a Stairway to Heaven says > a lot about anticipated

Re: [sqlite] How to get aggregate without reducing number of rows (repeats are ok)?

irway to Heaven says > a lot about anticipated traffic volume. > > > >-Original Message- > >From: sqlite-users [mailto:sqlite-users- > >boun...@mailinglists.sqlite.org] On Behalf Of Rocky Ji > >Sent: Sunday, 17 February, 2019 09:47 > >To: SQLite mailing

Re: [sqlite] How to get aggregate without reducing number of rows (repeats are ok)?

@Luuk that was my initial approach. But everyone advices against nested select statements. Can we do it without that sub-query? On Sun, Feb 17, 2019, 11:04 PM Luuk > On 17-2-2019 17:46, Rocky Ji wrote: > > Hello everyone, > > > > How can I prevent group by clause fro

[sqlite] How to get aggregate without reducing number of rows (repeats are ok)?

Hello everyone, How can I prevent group by clause from reducing the number of rows without affecting accuracy of what aggregate functions provide? Scenario: My club has-many coaches. Each coach trains a team of players. Of course, a player has-many matches and a match has-many players. Given the

Re: [sqlite] How to get all SQL statement syntax images?

I found the solutions to my issue, thanks everyone. How do I mark this thread [SOLVED] On Mon, Sep 3, 2018, 7:21 AM Warren Young wrote: > On Sep 2, 2018, at 4:15 AM, Richard Hipp wrote: > > > > On 9/1/18, Rocky Ji wrote: > >> Hi everyone, > >> > >> (

Re: [sqlite] How to get all SQL statement syntax images?

Wow! That is an amazing compilation, thanks. P.S. I am a big fan of your work. On Sun, Sep 2, 2018, 3:45 PM Richard Hipp wrote: > On 9/1/18, Rocky Ji wrote: > > Hi everyone, > > > > (Mailing list newbie here). Where can I get a corpus of all the > > -stmt.gif that a

[sqlite] How to get all SQL statement syntax images?

Hi everyone, (Mailing list newbie here). Where can I get a corpus of all the -stmt.gif that are shown in the online docs e.g. https://sqlite.org/images/syntax/insert-stmt.gif ? I tried using Google like - * filetype:gif site:sqlite.org/images/syntax/ but that didn't work. So how do I access all