How do I find the position of a character in a SQLite column?

If the result "joe@s开发者_高级运维tackoverflow.com" and I want to find the position of the @ symbol (3). Is it possible? It does not appear that SQLite has the equivalent of INSTR, LOCATE, POSITION or any such function.

SELECT ?('joe@stackoverflow.com')

Update I ended up registering a custom extension function, but was surprised that I had to.

Don't know whether this is a recent addition, but the INSTR function will indeed find the 1st instance.

Retrieve filename from a path:

select replace(path, rtrim(path, replace(path, '/', '' ) ), '') from example;

Got from http://community.spiceworks.com/topic/38836-sqlite-question-how-do-i-find-the-position-of-a-character-in-a-string?page=2, hope it could help someone.

SELECT instr(COLUMN, '@') FROM TABLE

As you can see here, charindex is not in SQLite standard package(At least in 3.6.2 version that i use). But this extension (extension-functions.c) has Charindex, which is basically the same as the VB instr.

To add these functions to sqlite:

We have to compile the C source code to make the sqlite extension (Assuming you are on Windows):

• Install the mingw compiler, small and easy to do.

• copy sqlite3.h to the same folder

• gcc -fPIC -lm -shared extension-functions.c -o libsqlitefunctions.dll

• fireup sqlite

• select load_extension('libsqlitefunctions.dll')

There are other string and math functions as well.

Got this from a spiceworks posting:

The first step is to remove all characters up to a specific character using ltrim or rtrim. If you're retrieving a filename then you trim all characters that are not slashes. If you're retrieving a username then you trim everything that is not an @ character. You can then use that trimmed string in a replace function to replace its occurrence with an empty string. That will provide you with every character that you just trimmed in the first step. In other words, the username or filename.

create table example (path varchar(256), email varchar(128));

insert into example values ('/path/to/file1.txt', 'user@domain.com');

Retrieve user from email:

select replace(email, ltrim(email, '1234567890qwertyuiopasdfghjklzxcvbnmQWERTYUIOPASDFGHJKLZXCVBNM!#$%^&*()_+-=`~[]\/{}|;:,.<>?'),'') from example;

Use charindex('c', column, 0)

it depends on the version of SQLite.

I'm using Python, but this can be done using the command line tools.

This solution uses Regex to extract a date:

import re
import sqlite3

def get_date_from_path(item: str, expr: str):
    return re.search(pattern=expr, string=item).group()

conn = sqlite3.connect(database=r'Name.db')
conn.create_function("GET_DATE_FROM_PATH", 2, get_date_from_path)

SELECT GET_DATE_FROM_PATH('C:\reports\report_20190731.csv', r'[0-9]{8}');

SELECT 
   email,
   POSITION('@' IN email)
From
   table

Here's a basic resource you might like: http://sqlzoo.net/howto/source/z.dir/tip238311/sqlite

Position of char '@' can be finded by function CHARINDEX (MSSQL):

SELECT CHARINDEX('@', 'joe@stackoverflow.com', 0)