CODEWITHSUNDEEP
sqlsqlitedb-browser-sqlite
Zuhairi Tusiman
Zuhairi Tusiman

Reputation: 1

Extracting text in string using DB browser (SQLite)

I want to extract the \\xxxxxxx04\xxx and \\yyyy03\yyyyyyy from these samples:

  1. \\\\xxxxxxx04\xxx\05. ***\*** **\*****\*****\****.pdf
  2. \\\\yyyy03\yyyyyyy\***\*** **\*****\*****\****.html

There are approximately 3,000,000 rows like this.

I am using DB Browser (SQLite), and apparently it doesn't recognize CHARINDEX.

In before, I have tried query

SELECT path
    SUBSTR(path, 3, INSTR(SUBSTR(path, 3), '\') - 1) AS server,
FROM results;

but it only returns text before the first \ (xxxxxxx04 and yyyy03).

How can I do this?

Upvotes: 0

Views: 148

Answers (1)

TristanMas
TristanMas

Reputation: 476

You are not far I think, maybe try this :

SELECT 
  SUBSTR(path, 
         INSTR(path, '\\'), 
         INSTR(SUBSTR(path, INSTR(path, '\\') + 1), '\\') + 1) AS extracted_text 
FROM 
  results;

INSTR(path, '\\') should find position of first backslash

SUBSTR(path, INSTR(path, '\\') + 1) should remove everything before first backslash

INSTR(SUBSTR(path, INSTR(path, '\\') + 1), '\\') should find position of next backslash

SUBSTR(path, INSTR(path, '\\'), INSTR(SUBSTR(path, INSTR(path, '\\') + 1), '\\') + 1) should extract everything between first and second backslash

Upvotes: 0

Related Questions