Extracting text in string using DB browser (SQLite)

Question

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?

Answer 1

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

Answer 2

This SQLite code utilizes the INSTR and SUBSTR functions to obtain the desired results. This query effectively handles matching pattern \\*\*.

SELECT 
    CASE
        WHEN INSTR(firstPath, '\\')  THEN NULL
        WHEN INSTR(secondPath, '\') > 0 THEN firstPath || SUBSTR(secondPath, 1, INSTR(secondPath, '\') - 1)
        WHEN INSTR(SecondPath, '*') > 0 THEN firstPath || SUBSTR(secondPath, 1, INSTR(secondPath, '*') - 1)
    END AS derivedPath, path currentPath
FROM
(
  SELECT 
        CASE WHEN NOT Path GLOB '\\*\*' THEN NULL 
        ELSE '\' || SUBSTR(path, 3, INSTR(SUBSTR(path, INSTR(PATH,'\\')+2 ), '\')) 
    END AS firstPath, 
    length('\\' || SUBSTR(path, 3, INSTR(SUBSTR(path, 3), '\')))+1 Len_second_slash,
    CASE WHEN NOT Path GLOB '\\*\*' THEN NULL 
        ELSE SUBSTR(path, length('\\' || SUBSTR(path, 3, INSTR(SUBSTR(path, 3), '\')))+1) 
    END AS secondPath, path
  FROM results Where Path GLOB '\\*\*'
) subquery;

    Path
    ------------------------------------------------
    \\yyyy03\yyyyyyy** ****.html ... 3,000,000 rows
    \\xxxxxxx04\xxx\05. * *******.pdf

Result

CurrentPath                                         derivedPath
-------------------------------------------------------------------
\\xxxxxxx04\xxx\05. * *******.pdf                   \xxxxxxx04\xxx
\\yyyy03\yyyyyyy** ****.html ... 3,000,000 rows     \yyyy03\yyyyyyy