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
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]
SQL Server Portal 
Thanks Muhammad, I needed to create an abbreviated acronym from a text field and this works perfectly š
I need help trying to combine characters from 2 different columns in one result.
[column1] [column2]
WAVEID INBOUNDTOTE
———— ———————–
201405120022 T0000002994
I want to grab the first 10 characters of column 1 and combine the last 4 characters of column2.
Desired results
———————
20140512002994
HERE IS MY INCORRECT SQL BELOW
—————————————————-
select eventtime as OK1, id as PK1, id as PK2,’CWMS0104′,’A’,id,id,
concat(waveid,1,10,inboundtote,8,11),
to_char(eventtime,’YYYYMMDD’), ”,”,’17532′, ”,
taskid,”, ‘P’, ‘PUTWALL’, ” , ‘4443’, locationid, ”, ”,”, ”,QTYPUT,QTYPUT,SKUID,”,”,”, ”,”, ”, ”, ”,”, ”, ”, ”, ”, ”,
operatorid,to_char(eventtime,’YYYYMMDDHHMMSS’), ”, ”, ”, ”, ”
from ohl_ptwl
where eventtime > to_date(‘20130224’, ‘YYYYMMDD’)
group by eventtime,id,taskid,locationid,qtyput,skuid,operatorid
ORDER BY eventtime,waveid;
HAD SOME FORMAT ISSUES PLEASE SEE COLUMNS BELOW
[column1]
WAVEID
———————–
201405120022
[column2]
INBOUNDTOTE
———————–
T0000002994
Hi,
I think you should try LEFT & RIGHT function to achieve it.
Imran
Hi imran, If I change the delimiter to ‘-‘, it is throwing error. ‘XML parsing: line 1, character 39, semicolon expected’
Given below script will get the first letter of each word from a string in SQL SERVER, how we can get the same in DB2..please suggest…
Thanks in Advance…
SELECT [Student Name],
dbo.[UDF_FIRST_LETTER_FROM_WORD] ([Student Name]) as [First Letters]
FROM Student
GO