SQL 如何在 SQLite 列中找到字符的位置？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6989895/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I find the position of a character in a SQLite column?
提问by tofutim
If the result "[email protected]" 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, POSITIONor any such function.
如果结果“ [email protected]”，我想找到@符号（3）的位置。是否可以？它不会出现的SQLite具有相当于INSTR，LOCATE，POSITION或任何这样的功能。
SELECT ?('[email protected]')
UpdateI ended up registering a custom extension function, but was surprised that I had to.
更新我最终注册了一个自定义扩展功能，但很惊讶我必须这样做。
回答by Pavel Polushkin
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.
回答by mediaczar
Don't know whether this is a recent addition, but the INSTRfunctionwill indeed find the 1st instance.
不知道这是否是最近添加的，但该INSTR函数确实会找到第一个实例。
回答by Robert Muil
Got this from a spiceworks posting:
从spiceworks 的帖子中得到这个：
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.
第一步是使用 ltrim 或 rtrim 删除直到特定字符的所有字符。如果您正在检索文件名，则修剪所有不是斜杠的字符。如果您正在检索用户名，那么您可以修剪所有不是 @ 字符的内容。然后，您可以在替换函数中使用修剪后的字符串，用空字符串替换它的出现。这将为您提供您在第一步中刚刚修剪的每个字符。换句话说，用户名或文件名。
create table example (path varchar(256), email varchar(128));
insert into example values ('/path/to/file1.txt', '[email protected]');
Retrieve user from email:
从电子邮件中检索用户：
select replace(email, ltrim(email, '1234567890qwertyuiopasdfghjklzxcvbnmQWERTYUIOPASDFGHJKLZXCVBNM!#$%^&*()_+-=`~[]\/{}|;:,.<>?'),'') from example;
回答by MojAmiri
As you can see here, charindex is not in SQLite standard package(At least in 3.6.2 version that i use). But thisextension (extension-functions.c) has Charindex, which is basically the same as the VB instr.
正如您在此处看到的，charindex 不在 SQLite 标准包中（至少在我使用的 3.6.2 版本中）。但是这个扩展（extension-functions.c）有Charindex，和VB的instr基本一样。
To add these functions to sqlite:
要将这些函数添加到 sqlite：
We have to compile the C source code to make the sqlite extension (Assuming you are on Windows):
我们必须编译 C 源代码来制作 sqlite 扩展（假设您使用的是 Windows）：
Install the mingwcompiler, 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')
安装mingw编译器，小而易做。
将 sqlite3.h 复制到同一个文件夹
gcc -fPIC -lm -shared extension-functions.c -o libsqlitefunctions.dll
启动sqlite
选择 load_extension('libsqlitefunctions.dll')
There are other string and math functions as well.
还有其他字符串和数学函数。
回答by PYLONEGG
SELECT instr(COLUMN, '@') FROM TABLE
从表中选择指令（列，'@'）
回答by jow corvo
Use charindex('c', column, 0)
用 charindex('c', column, 0)
it depends on the version of SQLite.
这取决于 SQLite 的版本。
回答by DonkeyKong
I'm using Python, but this can be done using the command line tools.
我正在使用 Python，但这可以使用命令行工具来完成。
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}');
回答by Chains
SELECT
email,
POSITION('@' IN email)
From
table
Here's a basic resource you might like: http://sqlzoo.net/howto/source/z.dir/tip238311/sqlite
这是您可能喜欢的基本资源：http: //sqlzoo.net/howto/source/z.dir/tip238311/sqlite
回答by DAlex
Position of char '@' can be finded by function CHARINDEX (MSSQL):
字符 '@' 的位置可以通过函数 CHARINDEX (MSSQL) 找到：
SELECT CHARINDEX('@', '[email protected]', 0)
