Grouping the first letter in a string

Question

Grouping the first letter in a string

sathishkm

SSC Veteran

Points: 239
More actions
March 23, 2023 at 5:36 pm

#4164264

Hi All,
I have the column of data in SQL Server. Here I need to group the letters of first word in the columns. Like the below in SQL Server
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Steve Collins SSCrazy Points: 2152 More actions · Answer 1

Per SQL 2022 you could try STRING_SPLIT with the optional ordinal parameter and then recombine using STRING_AGG

drop table if exists #temp
go
select * 
into #temp
from (values ('First American Equipment Finance'),
             ('United Wholesale Monkies'),
             ('Texas Instruments')) T(Col1);

select string_agg(left(ss.[value], 1), '') within group (order by ss.ordinal) first1_in_order
from #temp t
     cross apply string_split(t.Col1, ' ', 1) ss
group by Col1;

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

sathishkm SSC Veteran Points: 239 More actions · Answer 2

sathishkm

SSC Veteran

Points: 239

March 23, 2023 at 6:40 pm

#4164289

I get wrong result for the above code.

pietlinden SSC Guru Points: 63489 More actions · Answer 3

Care to explain? What do you get when you run it? Works for me.

Be specific. Show what you get.

sathishkm SSC Veteran Points: 239 More actions · Answer 4

This was the error message

Msg 8144, Level 16, State 3, Line 12

Procedure or function string_split has too many arguments specified.

Completion time: 2023-03-24T13:47:00.0877172+05:30

aitorzur@hotmail.com SSC Rookie Points: 32 More actions · Answer 5

I have broken up the solution for explanation. I broke it into 3 logical parts.

1) Use 'cross apply' with string_split to list each word for each CompanyName

2) Use left to extract the initial of each word

3) Using 'string_agg' to concatenate the initials grouping on CompanyName

Here it is in detail:

/*
--- setup

*/

drop table if exists #temp
go
select * 
into #temp
from (values ('First American Equipment Finance'),
 ('United Wholesale Monkies'),
 ('Texas Instruments')) T(CompanyName);


select CompanyName
, first1_in_order = string_agg(left(ss.[value], 1), '') within group (order by ss.ordinal) 
from #temp t
 cross apply string_split(t.CompanyName, ' ', 1) ss
group by CompanyName;





/*--- Breaking up the solution for explanation

1) Use 'cross apply' with string_split to list each word for each company name.
2) Use left to extract the initial of each word

*/

select CompanyName
, ss.value
, ss.ordinal  
, Initial = left([value], 1)
from #temp t
 cross apply string_split(t.CompanyName, ' ', 1) ss


CompanyName                      value                            ordinal
-------------------------------- -------------------------------- --------------------
First American Equipment Finance First                            1
First American Equipment Finance American                         2
First American Equipment Finance Equipment                        3
First American Equipment Finance Finance                          4
United Wholesale Monkies         United                           1
United Wholesale Monkies         Wholesale                        2
United Wholesale Monkies         Monkies                          3
Texas Instruments                Texas                            1
Texas Instruments                Instruments                      2

(9 rows affected)


Completion time: 2023-03-24T09:45:44.1896132-05:00


/* 
--- 3) Using 'string_agg' to concatenate the initials grouping on CompanyName

*/
select 
CompanyName
, Initials =  string_agg(Initial, '') within group (order by ordinal)
from (
select CompanyName
, ss.value
, ss.ordinal  
, Initial = left([value], 1)
from #temp t
 cross apply string_split(t.CompanyName, ' ', 1) ss
) t
group by CompanyName



CompanyName                      Initials
-------------------------------- -------------------------------------------
First American Equipment Finance FAEF
Texas Instruments                TI
United Wholesale Monkies         UWM

(3 rows affected)

Jeff Moden SSC Guru Points: 1004304 More actions · Answer 6

sathishkm wrote:

This was the error message
Msg 8144, Level 16, State 3, Line 12 Procedure or function string_split has too many arguments specified.
Completion time: 2023-03-24T13:47:00.0877172+05:30

That probably means that you're not actually using SQL Server 2022 or the compatibility level isn't correct for SQL Server 2022. This particular forum is explicitly for 2022. Please identify what version of SQL Server you're actually using and please pay attention as to which forum you're posting to in the future.

Also, please "Read'n'Heed" the article at the first link in my signature line below for future posts. It'll help a whole lot.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)