How do I find the position of a character in a SQL

Question

If the result "[e-mail]" 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 ?('[e-mail]')

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

Answer 1

Use charindex('c', column, 0)

it depends on the version of SQLite.

Answer 2

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

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

Answer 3

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}');

Answer 4

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

Answer 5

SELECT instr(COLUMN, '@') FROM TABLE

Answer 6

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.