Extract the first word of a string in a SQL Server query

Question

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

User · Answer

DECLARE  string NVARCHAR 50   SET  string    CUT STRING   SELECT LEFT  string  PATINDEX        string

User · Answer

Adding the following before the RETURN statement would solve for the cases where a leading space was included in the field   SET  Value   LTRIM RTRIM  Value

User · Answer

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   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

User · Answer

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   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

User · Answer

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   DECLARE  test NVARCHAR 255  SET  test    First Second   SELECT SUBSTRING LTRIM  test  1  CHARINDEX     LTRIM  test        -1

User · Answer

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

User · Answer

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

User · Answer

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   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

[tsql] Extract the first word of a string in a SQL Server query

Examples related to tsql