blocks|key|2941525|text|从路径检索文件名：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2941526|select+replace(path,+rtrim(path,+replace(path,+'/',+''+)+),+'')+from+example;|code-block|syntax|javascript|2941527|来自http://community.spiceworks.com/topic/38836-sqlite-question-how-do-i-find-the-position-of-a-character-in-a-string?page=2，希望能帮上忙。|offset|length|2941528|entityMap|0|LINK|mutability|MUTABLE|url|http://community.spiceworks.com/topic/38836-sqlite-question-how-do-i-find-the-position-of-a-character-in-a-string?page=2^0|0|0|2|3C|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@$I|V|J|W|1|X]]|A|$]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|L|$M|$5|N|O|P|A|$Q|R]]]]

Retrieve filename from a path:

<pre><code>select replace(path, rtrim(path, replace(path, '/', '' ) ), '') from example;
</code></pre>

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

blocks|key|2941563|text|不知道这是否是最近增加的，但函数确实会找到第一个实例。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|2941564|entityMap|0|LINK|mutability|MUTABLE|url|https://www.sqlite.org/lang_corefunc.html#instr^0|E|2|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@$A|M|B|N|1|O]]|C|$]]|$1|D|3|-4|5|6|7|P|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]]]

Don't know whether this is a recent addition, but the <a href="https://www.sqlite.org/lang_corefunc.html#instr" rel="noreferrer"><code>INSTR</code> function</a> will indeed find the 1st instance.

blocks|key|2941598|text|从表中选择instr(列，'@')|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2941599|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

SELECT instr(COLUMN, '@') FROM TABLE

blocks|key|2941424|text|这是从一个香料工厂的帖子得到的|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|2941425|第一步是使用ltrim或rtrim将所有字符移除到特定字符。如果您正在检索文件名，那么您将修剪所有不是斜杠的字符。如果您正在检索用户名，那么您将修剪所有不是@字符的内容。然后，您可以在替换函数中使用该修整字符串来用空字符串替换其出现。这将为您提供您刚刚修剪的第一步的每一个字符。换句话说，用户名或文件名。|blockquote|2941426|create+table+example+(path+varchar(256),+email+varchar(128));

insert+into+example+values+('/path/to/file1.txt',+'user@domain.com');|code-block|syntax|javascript|2941427|从电子邮件中检索用户：|style|BOLD|2941428|select+replace(email,+ltrim(email,+'1234567890qwertyuiopasdfghjklzxcvbnmQWERTYUIOPASDFGHJKLZXCVBNM!#$%25%5E&*()_%2B-=`~[]\/{}%7C;:,.<>?'),'')+from+example;|2941429|entityMap|0|LINK|mutability|MUTABLE|url|http://community.spiceworks.com/topic/38836-sqlite-question-how-do-i-find-the-position-of-a-character-in-a-string?page=2^0|3|9|0|0|0|0|0|B|0|0^^$0|@$1|2|3|4|5|6|7|Z|8|@]|9|@$A|10|B|11|1|12]]|C|$]]|$1|D|3|E|5|F|7|13|8|@]|9|@]|C|$]]|$1|G|3|H|5|I|7|14|8|@]|9|@]|C|$J|K]]|$1|L|3|M|5|F|7|15|8|@$A|16|B|17|N|O]]|9|@]|C|$]]|$1|P|3|Q|5|I|7|18|8|@]|9|@]|C|$J|K]]|$1|R|3|-4|5|6|7|19|8|@]|9|@]|C|$]]]|S|$T|$5|U|V|W|C|$X|Y]]]]

Got this from <a href="http://community.spiceworks.com/topic/38836-sqlite-question-how-do-i-find-the-position-of-a-character-in-a-string?page=2" rel="nofollow">a spiceworks posting</a>:

<blockquote>
 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.
</blockquote>

<pre><code>create table example (path varchar(256), email varchar(128));

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

<blockquote>
 Retrieve user from email:
</blockquote>

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

blocks|key|2941500|text|如您所见，charindex不在SQLite标准包中(至少在我使用的3.6.2版本中)。但是这扩展(Extension-Funcess.c)有Charindex，这与VB基本相同。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|2941501|要将这些函数添加到sqlite中，请执行以下操作：|2941502|我们必须编译C源代码以使sqlite扩展(假设您在Windows上)：|2941503|安装明威编译器，体积小，操作简单。|unordered-list-item|2941504|将sqlite3.h复制到同一个文件夹|2941505|gcc+-fPIC+-lm+-shared扩展-Functions.c+-o+libsqlitefunctions.dll|2941506|火烧方形石|2941507|选择load_extension('libsqlitefunctions.dll')|2941508|还有其他字符串和数学函数。|2941509|entityMap|0|LINK|mutability|MUTABLE|url|http://www.sqlite.org/contrib|1|http://www.mingw.org/category/wiki/download^0|1A|1|0|0|0|0|2|2|1|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|14|8|@]|9|@$A|15|B|16|1|17]]|C|$]]|$1|D|3|E|5|6|7|18|8|@]|9|@]|C|$]]|$1|F|3|G|5|6|7|19|8|@]|9|@]|C|$]]|$1|H|3|I|5|J|7|1A|8|@]|9|@$A|1B|B|1C|1|1D]]|C|$]]|$1|K|3|L|5|J|7|1E|8|@]|9|@]|C|$]]|$1|M|3|N|5|J|7|1F|8|@]|9|@]|C|$]]|$1|O|3|P|5|J|7|1G|8|@]|9|@]|C|$]]|$1|Q|3|R|5|J|7|1H|8|@]|9|@]|C|$]]|$1|S|3|T|5|6|7|1I|8|@]|9|@]|C|$]]|$1|U|3|-4|5|6|7|1J|8|@]|9|@]|C|$]]]|V|$W|$5|X|Y|Z|C|$10|11]]|12|$5|X|Y|Z|C|$10|13]]]]

As you can see <a href="http://www.sqlite.org/contrib" rel="nofollow">here</a>, charindex is not in SQLite standard package(At least in 3.6.2 version that i use). But <a href="http://www.sqlite.org/contrib" rel="nofollow">this</a> 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): 

<ul>
<li>Install the <a href="http://www.mingw.org/category/wiki/download" rel="nofollow">mingw</a> compiler, small and easy to do. </li>
<li>copy sqlite3.h to the same folder </li>
<li>gcc -fPIC -lm -shared extension-functions.c -o libsqlitefunctions.dll </li>
<li>fireup sqlite </li>
<li>select load_extension('libsqlitefunctions.dll') </li>
</ul>

There are other string and math functions as well.

blocks|key|1744688|text|我正在使用Python，但这可以使用命令行工具来完成。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1744689|此解决方案使用Regex提取日期：|1744690|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}');|code-block|syntax|javascript|1744691|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

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

This solution uses Regex to extract a date:

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

blocks|key|2941357|text|SELECT+
+++email,
+++POSITION('@'+IN+email)
From
+++table|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|2941358|下面是您可能喜欢的基本资源：http://sqlzoo.net/howto/source/z.dir/tip238311/sqlite|unstyled|offset|length|2941359|entityMap|0|LINK|mutability|MUTABLE|url|http://sqlzoo.net/howto/source/z.dir/tip238311/sqlite^0|0|E|1H|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|R|8|@]|9|@$G|S|H|T|1|U]]|A|$]]|$1|I|3|-4|5|F|7|V|8|@]|9|@]|A|$]]]|J|$K|$5|L|M|N|A|$O|P]]]]

<pre><code>SELECT 
 email,
 POSITION('@' IN email)
From
 table
</code></pre>

Here's a basic resource you might like: <a href="http://sqlzoo.net/howto/source/z.dir/tip238311/sqlite" rel="nofollow">http://sqlzoo.net/howto/source/z.dir/tip238311/sqlite</a>

blocks|key|3297676|text|CHARINDEX+(MSSQL)函数可以找到char+'@‘的位置：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3297677|SELECT+CHARINDEX('@',+'joe@stackoverflow.com',+0)|code-block|syntax|javascript|3297678|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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

<pre><code>SELECT CHARINDEX('@', 'joe@stackoverflow.com', 0)
</code></pre>

If the result "<code>joe@stackoverflow.com</code>" and I want to find the position of the <code>@</code> symbol (3). Is it possible? It does not appear that SQLite has the equivalent of <code>INSTR</code>, <code>LOCATE</code>, <code>POSITION</code> or any such function.

<pre><code>SELECT ?('joe@stackoverflow.com')
</code></pre>

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

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

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

如果结果"joe@stackoverflow.com“和我想找到@符号(3)的位置。有可能吗？SQLite似乎没有INSTR、LOCATE、POSITION或任何类似的函数。SELECT ?('joe@stackoverflow.com')Update我注册了一个自定义扩展函数，但我不得不注册，这让我感到惊讶。

问如何在SQLite列中找到字符的位置？
EN

回答 8

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在SQLite列中找到字符的位置？EN

回答 8

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在SQLite列中找到字符的位置？
EN