CODEWITHSUNDEEP
sqlsql-server
RalphBiggerton
RalphBiggerton

Reputation: 199

Selecting first letter of each word in a string in SQL

I have a column where I need to select the first letter of each word using SQL, with the output looking like the following

Table Name: Data

|  COLUMN  | OUTPUT | 
_____________________
| John Doe |   JD   |
| Bob Dole |   BD   |
| Dan Roby |   DR   |
_____________________

Choosing Left or Right via SQL would only select a specific letter based on length of strong, so unsure how to approach this

Any suggestions would be appreciated, previous examples in comments below don't work for Azure SQL Server given "regexp_split_to_table" isn't recognized and explicit case statements won't work for this example.

Upvotes: 1

Views: 3817

Answers (2)

Yitzhak Khabinsky
Yitzhak Khabinsky

Reputation: 22187

Please try the following solution. It will work starting from SQL Server 2017 onwards.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, col NVARCHAR(MAX));
INSERT INTO @tbl (col) VALUES
(N'John Doe'),
(N'Bob Dole'),
(N'Dan Roby'),
(N'Mary Smith Robert Polack');
-- DDL and sample data population, end

SELECT *,
     (SELECT STRING_AGG(LEFT(value,1), '')
            FROM STRING_SPLIT(col, SPACE(1))
            ) AS Result
FROM @tbl;

Output

+----+--------------------------+--------+
| ID |           col            | Result |
+----+--------------------------+--------+
|  1 | John Doe                 | JD     |
|  2 | Bob Dole                 | BD     |
|  3 | Dan Roby                 | DR     |
|  4 | Mary Smith Robert Polack | MSRP   |
+----+--------------------------+--------+

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269843

You can use string_split() and then bring them back together:

select t.*, x.inits
from t cross apply
     (select string_agg(left(s.value, 1), '') within group (order by ind) as inits
      from (select s.*, charindex(' ' + s.value + ' ', ' ' + t.name + ' ') as ind
            from string_split(t.name, ' ') s
           ) s
     ) x;

Here is a db<>fiddle.

Aaron Bertrand insists that the more recent versions of SQL Server keep results of string_split() in order. However, until this is represented in the documentation, I would be hesitant to use it.

Upvotes: 2

Related Questions