Extract first word in a SQLite3 database

Question

I have a SQLITE3 database wherein I have stored various columns. One column (cmd) in particular contains the full command line and associated parameters. Is there a way to extract just the first word in this column (just before the first space)? I am not interested in seeing the various parameters used, but do want to see the command issued.

Here's an example:

select cmd from log2 limit 3;

  user-sync //depot/PATH/interface.h
  user-info
  user-changes -s submitted //depot/PATH/build/...@2011/12/06:18:31:10,@2012/01/18:00:05:55

From the result above, I'd like to use an inline SQL function (if available in SQLITE3) to parse on the first instance of space, and perhaps use a left function call (I know this is not available in SQLITE3) to return just the "user-sync" string. Same for "user-info" and "user-changes".

Any ideas?

Thanks.

Answer 1

My soluion:

sqlite> CREATE TABLE command (cmd TEXT);
sqlite> INSERT INTO command (cmd) VALUES ('ls'),('cd ~'),('  mpv movie.mkv  ');
sqlite> SELECT substr(trim(cmd),1,instr(trim(cmd)||' ',' ')-1) FROM command;
ls
cd
mpv

Pros:

• it's not that a dirty hack
• it only uses core functions

Answer 2

"Finds the first occurrence" function is one of the SQLite3 Core Functions (http://www.sqlite.org/lang_corefunc.html).

Of course, it is much better to use instr(X,Y).

So you can write:

SELECT substr(cmd,1,instr(cmd,' ')-1) FROM log2