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

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

SELECT CHARINDEX('@', '[e-mail]', 0)

Answer 2

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', '[e-mail]');

Retrieve user from email:

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

Answer 3

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.