Select like and unlike record pattern SQL

selectsqlite

Lets assume I got this table, that got file pathes :

id    file                
 1     /etc/file.txt      
 2     /etc/.delete.file.txt      
 3     /etc/secondfile.txt      
 4     /etc/four.txt      
 5     /etc/five.txt      
 6     /etc.delete.five.txt

I want to get all the files that are not deleted,

So if there is a file : .delete.file.txt -> I don't want to select both .delete.file.txt and also file.txt (always the prefix is .deleted)

Expected result after query :

id    file                
 3     /etc/secondfile.txt      
 4     /etc/four.txt

How can I do that?
(Using SQLITE)

Best Answer

Assuming that your file column is unique, you can do this:

SELECT
REPLACE(file, '.delete.', '') AS file
FROM your_table
GROUP BY REPLACE(file, '.delete.', '')
HAVING COUNT(*) = 1

You "delete" the ".delete." and then return only the rows that don't appear multiple times.

Related Solutions

How to select data and the total to cursors

An alternative to msi77's solution is the use of analytical functions. This might be a bit more efficient but you should run an explain plan to find out.

SELECT id,
       name, 
       count(*) over () as total_count
FROM your_table
WHERE name = 'AA'

Store a small log file as a blob in sqlite

For me your last line contradicts the subject (blob vs text), so probably I mistook your idea. But what about making your log file an INSERT statement on the fly? Then feed that into your desired sqlite database. I mean something like this:

$ cat test.sql
INSERT INTO test
SELECT 'something
anything';

$ sqlite3 test < test.sql

$ sqlite3 test
SQLite version 3.6.22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> SELECT * FROM test;
something
anything
sqlite>

Best Answer

Related Solutions

How to select data and the total to cursors

Store a small log file as a blob in sqlite

Related Question