CODEWITHSUNDEEP
sqlite
farhany
farhany

Reputation: 1521

Extract first word in a SQLite3 database

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.

Upvotes: 5

Views: 3994

Answers (5)

Lénárd Szolnoki
Lénárd Szolnoki

Reputation: 96

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

Upvotes: 8

Yahya
Yahya

Reputation: 308

"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

Upvotes: 5

Doug Currie
Doug Currie

Reputation: 41170

Here's a hack

sqlite> create table test (a);
sqlite> insert into test values ("This is a test.");
sqlite> select * from test;
This is a test.
sqlite> select rtrim(substr(replace(a,' ','----------------------------------------------------------------------------------------'),1,80),'-') from test;
This

It works as long as your longest command is less than 80 characters (and you include 80 '-' characters in the substitution string -- I didn't count them!). If your commands can contain '-' just use a different character that is not allowed in the commands.

Upvotes: 2

tomato
tomato

Reputation: 3383

I don't believe that's something you'll be able to do within the SQL itself. SQLite's support for string handling functions is not as extensive as other RDBMSs (some of which would let you do a SUBSTR with a Reg Exp).

My suggestion is either to write your own SQL function as suggested by @Jon or just do it as a post-processing step in your app code.

Upvotes: 0

jon
jon

Reputation: 6246

As the position of your first space character is unknown, I don't think there is a corefunction in SQLite that will help.

I think you'll have to create one http://www.sqlite.org/c3ref/create_function.html

Upvotes: 3

Related Questions