How to get the first letter of each word in SQL-CodePudding

I tried to run this query to get the initial letter of each word, and it worked for strings of 4 words, yet if the string has only two words, it duplicates the second word's initial.

select
  substring(column_name, 1, 1)   
  case 
    when 0 <>  charindex(' ', column_name)   1
      then substring(column_name, charindex(' ',column_name)   1, 1)
    else ''
  end  
  case 
    when 0 <>  charindex(' ', column_name, charindex(' ', column_name)   1) 
      then substring(column_name, charindex(' ', column_name, charindex(' ', column_name)   1)   1, 1)
    else ''
  end   
  case
    when 0 <> charindex(' ', column_name, charindex(' ', column_name, charindex(' ', column_name)   1)   1)
      then substring(column_name, charindex(' ', column_name, charindex(' ', column_name, charindex(' ', column_name)   1)   1)   1, 1) 
    else '' 
  end

from table_name

CodePudding user response：

You didn't specify which RDBMS you are using. This should work in SQL Server:

drop table if exists table_name

create table table_name (
  column_name varchar(255)
)

insert table_name
values ('See Jane')
     , ('See Jane run')
     , ('See Jane run and jump over the lazy dog.')


select stuff((SELECT ''   t2.fc
from (
    select left(str.value, 1) fc
    , charindex(' '   str.value   ' ', ' '   t.column_name   ' ') idx
    from string_split(t.column_name, ' ') str
) t2
order by t2.idx
FOR XML PATH('')
), 1, 0, '') as FirstChars
from table_name t

The idx column is used to order the ouptut because string_split does not promise to return the results in any particular order. Thanks to Aaron Bertrand - https://dba.stackexchange.com/questions/207274/string-split-and-ordered-results

CodePudding user response：

Given the use of charindex in your question, I'm assuming you are using SQL Server. The CTE generates a tall view of your data using string_split function, with each letter on it's own row. We then select from it and group by id, and apply the string_agg function to place back into a single row.

Password guessing?

create table my_data (
  id integer, 
  comments varchar(50)
  );

insert into my_data (id, comments) values 
(1, 'Thank goodness its friday'), 
(2, 'I want 2 scoops of ice cream');

select * from my_data;

id	comments
1	Thank goodness its friday
2	I want 2 scoops of ice cream

with cte (id, first_char) as (
  select id, substring(ss.value, 1, 1) as first_char
  from my_data
  cross apply string_split(comments, ' ')ss
  )
select t.id, 
  string_agg(t.first_char, ',') as letters_delimited, 
  string_agg(t.first_char, '') as letters_not_delimited
from cte t
group by t.id

id	letters_delimited	letters_not_delimited
1	T,g,i,f	Tgif
2	I,w,2,s,o,i,c	Iw2soic

fiddle here

CodePudding user response：

You can extend your approach with recursion

WITH cte_name AS (
    select CONVERT(nvarchar(max), substring(column_name, 1, 1)) conc, 
           column_name n, 
           charindex(' ', column_name, 0) pos 
    from table_name

    UNION ALL 
    select conc   substring(n, pos   1, 1) as conc, 
           n, 
           charindex(' ', n, pos   1) pos 
    from cte_name where pos > 0
)
 
SELECT *
FROM  cte_name
where pos = 0;