字符串中的前两个词 - sql server [英] First two words in the string - sql server

问题描述

我有这样的字符串这是一个你好世界的例子"

现在我想要句子的前两个词作为我在 SQL Server 中的输出.即这是 .

另一个例子:原句:《全词练习》输出:完整的单词

解决方案

对于少于 2 个单词的任何字符串，许多解决方案都会中断，这对于希望解析第一个 的人来说越来越有可能em>n 字数.

让我们首先看一下查询，然后我们如何判断它是否实际计算正确.
为此，我们需要嵌套多个

Pop quiz...上述查询将解析为什么?_{悬停在下方寻找答案}

SELECT LEFT('一二三', 3) -- '一'

在从sql中的字符串中提取特定数量的单词的解决方案中，我们可以检查最后一个 CHARINDEX 是否产生了一个非零值，这意味着它在该深度级别碰到了一个空格.而且，由于嵌套的 charindex 有点笨拙，我们可以通过 计算 a 的出现次数来更直接地获取该信息SQL varchar 中的某个子字符串?

所以最终的解决方案应该是这样的，以获取前4个词:

DECLARE @string VARCHAR(1000) = '一二三四五六';SELECT CASE WHEN(@string)-LEN(REPLACE(@string, ' ', '')) <4然后@stringELSE LEFT(@string, CHARINDEX(' ', @string,CHARINDEX(' ', @string,CHARINDEX(' ', @string,CHARINDEX(' ', @string)+1)+1)+1))结尾

如果少于 4 个空格，我们将只返回整个字符串.对于四个以上，我们会找到第 4 个空格的位置，并将字符串的左边部分一直返回到那个位置

I have string like this " This is a hello world example"

Now I want first two words of the sentence as my output in SQL Server. i.e. This is .

Another example: Original sentence : "Complete word exercise" Output: Complete word

解决方案

Many solutions will break for any strings that have less than 2 words, which is increasingly likely for people hoping to parse the first n number of words.

Let's first look at the query, and then how we can tell if it actually evaluated correctly.
For that, we need to nest multiple CHARINDEX statements, which take the following params:

CHARINDEX( expressionToFind , expressionToSearch [ , start_location ] )

Charindex will return the first index where it finds the specific string. What we keep doing is kicking the ball down the road by adding in a start_location equal to the first found instance +1 so it'll find the 2nd, 3rd, 4th instance, etc. Also, instead of SUBSTRING(@str, 0,... we can just use LEFT(@str,... to capture the first portion of the string, but calculating how far deep to go is the hard part anyway:

DECLARE @string VARCHAR(1000) = 'One Two Three';
SELECT LEFT(@string, CHARINDEX(' ', @string,
                     CHARINDEX(' ', @string,
                     CHARINDEX(' ', @string,
                     CHARINDEX(' ', @string)+1)+1)+1))

But this will fail if we don't have the minimum number of words:
^{Technically, it'll just keep looping around infinitely. Once it runs out of spaces, it'll start indexing again from the beginning}

Pop quiz... what will the above query resolve to? _{Hover below for answer}

SELECT LEFT('One Two Three', 3) -- 'One'

In the solution in Extracting a specific number of words from a string in sql, we can check that the last CHARINDEX produced a non-zero value, meaning it hit a space at that level of depth. But also, since the nested charindex is kind of unwieldy, we can get at that information at little more directly by counting the number of occurrences of a certain substring in a SQL varchar?

So the final solution should look like this to grab the first 4 words:

DECLARE @string VARCHAR(1000) = 'One Two Three Four Five Six';

SELECT CASE WHEN LEN(@string)-LEN(REPLACE(@string, ' ', '')) < 4 
            THEN @string
            ELSE LEFT(@string, CHARINDEX(' ', @string,
                               CHARINDEX(' ', @string,
                               CHARINDEX(' ', @string,
                               CHARINDEX(' ', @string)+1)+1)+1))
       END

If there are less than 4 spaces, we'll just return the whole string. For more than four, we'll find the position of the 4th space and return the left portion of the string all the way to that position

这篇关于字符串中的前两个词 - sql server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！