Feeds:
Posts
Comments

Posts Tagged ‘SQL SERVER – Get the first letter of each word in a String (Column)’

String manipulation in a different format is one of my favorite activities. Today, I came across a question in a forum, how to get the first letter of each word in a column. Obviously, my primary search was the internet, where I found few solutions, but all of them were loop based. HenceĀ  developed a solution without loop.

Give below is the script :

--Create a Sample
Create Table Student
(
[Student ID] int Identity(1,1),
[Student Name] varchar(50)
)
Go
Insert into Student Values ('Steve Masters Bob')
Insert into Student Values ('David Ortiz')
Insert into Student Values ('Michael Sean Ray')
Insert into Student Values ('Steven SElikoff')
Insert into Student Values ('Carole POLAND')
Insert into Student Values ('Bjorn Rettig')
Insert into Student Values ('Michiko OSAda')
Insert into Student Values ('CarOL Philips')
Insert into Student Values ('Merav Netz')
GO
--Create the procedure to get the first letter of each word.
Create Function dbo.[UDF_FIRST_LETTER_FROM_WORD]
(
   @String Varchar(Max) -- Variable for string
)
RETURNS Varchar(Max)
BEGIN
Declare @Xml Xml
Declare @firstletter Varchar(Max)
Declare @delimiter Varchar(5)

SET @delimiter=' '
SET @Xml = cast(('<a>'+replace(@String,@delimiter,'</a><a>')+'</a>') AS XML)

;With CTE AS (SELECT A.value('.', 'varchar(max)') as [Column]
FROM @Xml.nodes('a') AS FN(a) )
SELECT @firstletter =Stuff((SELECT '' + LEFT([Column],1)
FROM CTE
FOR XML PATH('') ),1,0,'')

RETURN (@firstletter)
END
GO

Example 1 :
Given below script will get the first letter of each word from a column of a table.

SELECT [Student Name],
dbo.[UDF_FIRST_LETTER_FROM_WORD] ([Student Name]) as [First Letters]
FROM Student
GO

firstletterfromword1.1

Example 2 :
Given below script will get the first letter of each word from a string.

Declare @String as varchar(100)
Set @String ='My Best Friend'
SELECT @String as [String]
, dbo.[UDF_FIRST_LETTER_FROM_WORD] (@String) as [First Letters]

firstletterfromword1.2

Read Full Post »