在SQL Server查询中提取字符串的第一个单词

What's the best way to extract the first word of a string in sql server query?

在sql server查询中提取字符串的第一个单词的最佳方法是什么?

8 个解决方案

#1

SELECT CASE CHARINDEX(' ', @Foo, 1)
     WHEN 0 THEN @Foo -- empty or single word
     ELSE SUBSTRING(@Foo, 1, CHARINDEX(' ', @Foo, 1) - 1) -- multi-word
END

You could perhaps use this in a UDF:

您也许可以在UDF中使用它:

CREATE FUNCTION [dbo].[FirstWord] (@value varchar(max))
RETURNS varchar(max)
AS
BEGIN
    RETURN CASE CHARINDEX(' ', @value, 1)
        WHEN 0 THEN @value
        ELSE SUBSTRING(@value, 1, CHARINDEX(' ', @value, 1) - 1) END
END
GO -- test:
SELECT dbo.FirstWord(NULL)
SELECT dbo.FirstWord('')
SELECT dbo.FirstWord('abc')
SELECT dbo.FirstWord('abc def')
SELECT dbo.FirstWord('abc def ghi')

#2

I wanted to do something like this without making a separate function, and came up with this simple one-line approach:

我想在没有单独功能的情况下做这样的事情,并提出了这种简单的单线方法:

DECLARE @test NVARCHAR(255)
SET @test = 'First Second'

SELECT SUBSTRING(@test,1,(CHARINDEX(' ',@test + ' ')-1))

This would return the result "First"

这将返回结果“First”

It's short, just not as robust, as it assumes your string doesn't start with a space. It will handle one-word inputs, multi-word inputs, and empty string or NULL inputs.

它很短,只是不那么健壮,因为它假设你的字符串不是以空格开头。它将处理单字输入,多字输入和空字符串或NULL输入。

#3

Adding the following before the RETURN statement would solve for the cases where a leading space was included in the field:

在RETURN语句之前添加以下内容将解决字段中包含前导空格的情况:

SET @Value = LTRIM(RTRIM(@Value))

#4

Enhancement of Ben Brandt's answer to compensate even if the string starts with space by applying LTRIM(). Tried to edit his answer but rejected, so I am now posting it here separately.

通过应用LTRIM(),即使字符串以空格开头,也增强了Ben Brandt的补偿。试图编辑他的答案但拒绝,所以我现在分别在这里发布。

DECLARE @test NVARCHAR(255)
SET @test = 'First Second'

SELECT SUBSTRING(LTRIM(@test),1,(CHARINDEX(' ',LTRIM(@test) + ' ')-1))

#5

DECLARE @string NVARCHAR(50)

SET @string = 'CUT STRING'

SELECT LEFT(@string,(PATINDEX('% %',@string)))

#6

Marc's answer got me most of the way to what I needed, but I had to go with patIndex rather than charIndex because sometimes characters other than spaces mark the ends of my data's words. Here I'm using '%[ /-]%' to look for space, slash, or dash.

Marc的回答让我得到了我所需要的大部分内容,但我不得不使用patIndex而不是charIndex,因为有时候除了空格之外的字符标记了数据的结尾。在这里,我使用'%[/ - ]%'来查找空格,斜线或破折号。

Select race_id, race_description
    , Case patIndex ('%[ /-]%', LTrim (race_description))
        When 0 Then LTrim (race_description)
        Else substring (LTrim (race_description), 1, patIndex ('%[ /-]%', LTrim (race_description)) - 1)
    End race_abbreviation
from tbl_races

Results...

race_id  race_description           race_abbreviation
-------  -------------------------  -----------------
1        White                      White
2        Black or African American  Black
3        Hispanic/Latino            Hispanic

Caveat: this is for a small data set (US federal race reporting categories); I don't know what would happen to performance when scaled up to huge numbers.

警告:这是一个小数据集(美国联邦竞赛报告类别);我不知道在扩大到数量时会对性能产生什么影响。

#7

A slight tweak to the function returns the next word from a start point in the entry

    CREATE FUNCTION [dbo].[GetWord] 
    (
        @value varchar(max)
        , @startLocation int
    ) 
    RETURNS varchar(max) 
    AS 
      BEGIN 

         SET @value = LTRIM(RTRIM(@Value))  
         SELECT @startLocation = 
                CASE 
                    WHEN @startLocation > Len(@value) THEN LEN(@value) 
                    ELSE @startLocation 
                END

            SELECT @value = 
                CASE 
                    WHEN @startLocation > 1 
                        THEN LTRIM(RTRIM(RIGHT(@value, LEN(@value) - @startLocation)))
                    ELSE @value
                END

            RETURN CASE CHARINDEX(' ', @value, 1) 
                    WHEN 0 THEN @value 
                    ELSE SUBSTRING(@value, 1, CHARINDEX(' ', @value, 1) - 1) 
                END

     END 
    GO

    SELECT dbo.GetWord(NULL, 1) 
    SELECT dbo.GetWord('', 1) 
    SELECT dbo.GetWord('abc', 1) 
    SELECT dbo.GetWord('abc def', 4) 
    SELECT dbo.GetWord('abc def ghi', 20)

#8

Try This:

Select race_id, race_description
, Case patIndex ('%[ /-]%', LTrim (race_description))
    When 0 Then LTrim (race_description)
    Else substring (LTrim (race_description), 1, patIndex ('%[ /-]%', LTrim (race_description)) - 1)
End race_abbreviation

from tbl_races

#1

SELECT CASE CHARINDEX(' ', @Foo, 1)
     WHEN 0 THEN @Foo -- empty or single word
     ELSE SUBSTRING(@Foo, 1, CHARINDEX(' ', @Foo, 1) - 1) -- multi-word
END