如何从SQL表中获取子字符串？ [英] How to get substring from a sql table?

问题描述

所以我在postgres表中有一个列（称为account_uri），如下所示：

So I have a column (called account_uri) in a postgres table that looks like this:

/randomCharacters/123456/randomNumbers

我需要查询中间的子字符串，这是两个 / 符号。

I need to query for the substring in the middle, which is a string of characters between two / symbols.

我当前的尝试如下：

SELECT 
REVERSE(SUBSTRING(REVERSE([account_uri]),0,CHARINDEX('/',REVERSE(account_uri))))
FROM exp_logs
LIMIT 15

仅选择randomNumber而不选择所需的数字。

Which selects only the randomNumbers and not the desired numbers.

尽管如此，我还是尝试建立并使用

I tried to build on that idea though and used

(SUBSTRING(REVERSE(SUBSTRING(REVERSE([account_uri]),CHARINDEX('/',REVERSE(account_uri)))),1,CHARINDEX('/',REVERSE(SUBSTRING(REVERSE([account_uri]),CHARINDEX('/',REVERSE(account_uri)))))))

但这只会返回一堆 / 符号，并且没有数字l。

but that only returns a bunch of / symbols and no numbers at all.

如果有人可以帮助我查询此子字符串，我将不胜感激

If anyone can help me query for this substring, I would be immensely grateful

推荐答案

select
  split_part(account_url, '/', 3)
from exp_logs;

有效。

http： //www.postgresql.org/docs/9.3/static/functions-string.html

兼容性：8.3 +

提琴： http://sqlfiddle.com/#!15/5e931/ 1

这篇关于如何从SQL表中获取子字符串？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！