oracle 如何使用空格分隔符获取任何通用单词或句子中的第 n 个字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10109335/
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 to get the nth string in any generic word or sentence with a space delimiter
提问by Teja
How do I get the nth word in a sentence or a set of strings with space delimiter?
如何获取句子或一组带有空格分隔符的字符串中的第 n 个单词？
Sorry for the change in the requirement.Thank you.
抱歉更改要求。谢谢。
回答by Ben
By using instr.
通过使用instr.
select substr(help, 1, instr(help,' ') - 1)
from ( select 'hello my name is...' as help
from dual )
instr(help,' ')returns the positional index of the first occurrence of the second argument in the first, inclusive of the string you're searching for. i.e. the first occurrence of ' 'in the string 'hello my name is...'plusthe space.
instr(help,' ')返回第一个参数中第二个参数第一次出现的位置索引，包括您正在搜索的字符串。即' '字符串中的第一次出现'hello my name is...'加上空格。
substr(help, 1, instr(help,' ') - 1)then takes the input string from the first character to the index indicated in instr(.... I then remove one so that the space isn't included..
substr(help, 1, instr(help,' ') - 1)然后将输入字符串从第一个字符到 中指示的索引instr(...。然后我删除一个，以便不包括空间..
For the nth occurrence just change this slightly:
对于第 n 次，只需稍微更改一下：
instr(help,' ',1,n)is the nthoccurrence of ' 'from the first character. You then need to find the positional index of the next index instr(help,' ',1,n + 1), lastly work out the difference between them so you know how far to go in your substr(.... As you're looking for the nth, when nis 1 this breaks down and you have to deal with it, like so:
instr(help,' ',1,n)是从第一个字符开始的第 n次出现' '。然后你需要找到下一个索引的位置索引instr(help,' ',1,n + 1)，最后计算出它们之间的差异，这样你就知道你的substr(.... 当您正在寻找nth 时，当n为 1 时，这会崩溃，您必须处理它，如下所示：
select substr( help
, decode( n
, 1, 1
, instr(help, ' ', 1, n - 1) + 1
)
, decode( &1
, 1, instr(help, ' ', 1, n ) - 1
, instr(help, ' ', 1, n) - instr(help, ' ', 1, n - 1) - 1
)
)
from ( select 'hello my name is...' as help
from dual )
This will also break down at n. As you can see this is getting ridiculous so you might want to consider using regular expressions
这也将在n处分解。如您所见，这变得很荒谬，因此您可能需要考虑使用regular expressions
select regexp_substr(help, '[^[:space:]]+', 1, n )
from ( select 'hello my name is...' as help
from dual )
回答by Dan A.
Try this. An example of getting the 4th word:
尝试这个。获取第 4 个单词的示例：
select names from (
select
regexp_substr('I want my two dollars','[^ ]+', 1, level) as names,
rownum as nth
from dual
connect by regexp_substr('I want my two dollars', '[^ ]+', 1, level) is not null
)
where nth = 4;
The inner query is converting the space-delimited string into a set of rows. The outer query is grabbing the nth item from the set.
内部查询将以空格分隔的字符串转换为一组行。外部查询从集合中抓取第 n 个项目。
回答by Bob Jarvis - Reinstate Monica
Try something like
尝试类似
WITH q AS (SELECT 'ABCD EFGH IJKL' AS A_STRING FROM DUAL)
SELECT SUBSTR(A_STRING, 1, INSTR(A_STRING, ' ')-1)
FROM q
Share and enjoy.
分享和享受。
And here's the solution for the revised question:
这是修改后的问题的解决方案：
WITH q AS (SELECT 'ABCD EFGH IJKL' AS A_STRING, 3 AS OCCURRENCE FROM DUAL)
SELECT SUBSTR(A_STRING,
CASE
WHEN OCCURRENCE=1 THEN 1
ELSE INSTR(A_STRING, ' ', 1, OCCURRENCE-1)+1
END,
CASE
WHEN INSTR(A_STRING, ' ', 1, OCCURRENCE) = 0 THEN LENGTH(A_STRING)
ELSE INSTR(A_STRING, ' ', 1, OCCURRENCE) - CASE
WHEN OCCURRENCE=1 THEN 0
ELSE INSTR(A_STRING, ' ', 1, OCCURRENCE-1)
END - 1
END)
FROM q;
Share and enjoy.
分享和享受。
回答by NIT
CREATE PROC spGetCharactersInAStrings ( @S VARCHAR(100) = '^1402 WSN NI^AMLAB^tev^e^^rtS htimS 0055518', @Char VARCHAR(100) = '8' ) AS -- exec spGetCharactersInAStrings '^1402 WSN NI^AMLAB^tev^e^^rtS htimS 0055518', '5' BEGIN DECLARE @i INT = 1, @c INT, @pos INT = 0, @NewStr VARCHAR(100), @sql NVARCHAR(100), @ParmDefinition nvarchar(500) = N'@retvalOUT int OUTPUT'
CREATE PROC spGetCharactersInAStrings ( @S VARCHAR(100) = '^1402 WSN NI^AMLAB^tev^e^^rtS htimS 0055518', @Char VARCHAR(100) = '8' ) AS -- exec spGetCharactersInAStrings2 WSN^14 ^AMLAB^tev^e^^rtS htimS 0055518', '5' BEGIN DECLARE @i INT = 1, @c INT, @pos INT = 0, @NewStr VARCHAR(100), @sql NVARCHAR(100), @ParmDefinition nvarchar(500) = N'@retvalOUT int OUTPUT'
DECLARE @D TABLE
(
ID INT IDENTITY(1, 1),
String VARCHAR(100),
Position INT
)
SELECT @c = LEN(@S), @NewStr = @S
WHILE @i <= @c
BEGIN
SET @sql = ''
SET @sql = ' SELECT @retvalOUT = CHARINDEX(''' + + @Char + ''',''' + @NewStr + ''')'
EXEC sp_executesql @sql, @ParmDefinition, @retvalOUT=@i OUTPUT;
IF @i > 0
BEGIN
set @pos = @pos + @i
SELECT @NewStr = SUBSTRING(@NewStr, @i + 1, LEN(@S))
--SELECT @NewStr '@NewStr', @Char '@Char', @pos '@pos', @sql '@sql'
--SELECT @NewStr '@NewStr', @pos '@pos'
INSERT INTO @D
SELECT @NewStr, @pos
SET @i = @i + 1
END
ELSE
BREAK
END
SELECT * FROM @D
END
结尾
回答by JustAC0der
If you're using MySQL and cannot use the instr function that accepts four parameters or regexp_substr, you can do this way:
如果您使用的是 MySQL 并且无法使用接受四个参数或 regexp_substr 的 instr 函数，您可以这样做：
select substring_index(substring_index(help, ' ', 2), ' ', -1)
from (select 'hello my name is...' as help) h
Result: "my".
结果：“我的”。
Replace "2" in the code above with the number of the word you want.
将上面代码中的“2”替换为您想要的单词的编号。
回答by Kevin
If you are using SQL Server 2016+ then you can take advantage of the STRING_SPLIT function. It returns rows of string values and if you aim to get nth value, then you can use Row_Number() window function.
如果您使用的是 SQL Server 2016+，那么您可以利用 STRING_SPLIT 函数。它返回字符串值的行，如果您的目标是获得第 n 个值，那么您可以使用 Row_Number() 窗口函数。
Here there is a little trick as you don't want to really order by something so that you have to "cheat" the row_number function and allow its value in the natural order which is the STRING_SPLIT() function will spit out.
这里有一个小技巧，因为您不想真正按某些东西排序，因此您必须“欺骗” row_number 函数并允许其值按自然顺序排列，即 STRING_SPLIT() 函数将吐出。
Below is a code snippet if you want to find the third word of the string
如果您想找到字符串的第三个单词，下面是一个代码片段
Declare @_intPart INT = 3; -- change nth work here, start # from 1 not 0
SELECT value FROM(
SELECT value,
ROW_NUMBER()OVER(ORDER BY (SELECT 1)) AS rowno
FROM STRING_SPLIT('hello world this is amazing', ' ')
) AS o1 WHERE o1.rowno = @_intPart;
You can also make a scalar function to retrieve values.
您还可以创建一个标量函数来检索值。
