Get the first letter of each word in a SQL string

Question

Possible duplicate:
sql to highlight a face name string and display initials

In MS-SQL Server , is there a way to get the first letter of each word in a string? For instance:

Name:

Michael Joseph Jackson

Request:

 SELECT name, [function] as initial FROM Customers 

Result:

Mjj

Answer 1

This function will protect your results from several consecutive spaces in the source line:

 CREATE FUNCTION dbo.fnFirsties ( @str NVARCHAR(4000) ) RETURNS NVARCHAR(2000) AS BEGIN DECLARE @retval NVARCHAR(2000); SET @str=RTRIM(LTRIM(@str)); SET @retval=LEFT(@str,1); WHILE CHARINDEX(' ',@str,1)>0 BEGIN SET @str=LTRIM(RIGHT(@str,LEN(@str)-CHARINDEX(' ',@str,1))); SET @retval+=LEFT(@str,1); END RETURN @retval; END GO SELECT dbo.fnFirsties('Michael Joseph Jackson'); SELECT dbo.fnFirsties(' Michael Joseph Jackson '); -- multiple space protection :) 

Results:

 MJJ MJJ 

Answer 2

You want to add some validation and error handling before updating tblStudents or something like that, but this should get you started.

 CREATE FUNCTION initials ( @s AS nvarchar(4000)) RETURNS nvarchar(100) AS BEGIN DECLARE @i nvarchar(100) = LEFT(@s, 1); -- first char in string DECLARE @p int = CHARINDEX(' ', @s); -- location of first space WHILE (@p > 0) -- while a space has been found BEGIN SET @i = @i + SUBSTRING(@s, @p + 1, 1) -- add char after space SET @p = CHARINDEX(' ', @s, @p + 1); -- find next space END RETURN @i END GO SELECT dbo.initials('Michael Joseph Jackson'); 

Answer 3

Assuming we are doing this in MSSQL2008R2, although this is nothing important. All we have is entertainment through string manipulation. You can put this in funciton or proc or just run it in the query analyzer directly.

 DECLARE @str varchar(250) = 'Michael Joseph Jackson' DECLARE @initials varchar(250) = substring(@str,1,1) WHILE(charindex(' ',@str)!=0) BEGIN DECLARE @currentSpace int = charindex(' ',@str) SET @initials += substring(@str,@currentSpace+1,1) SET @str = substring(@str,@currentSpace+1,len(@str)) END SELECT @initials 

If you are not doing this for some trivial purpose, you will most likely want to clear the data before trying to process it. Names are often prefixed with names, data entry fields are subject to user errors, etc.

Answer 4

You may need to use UDF: Custom Function

Answer 5

First you need a table function that breaks varchar and returns a table with one column named "S".

 CREATE FUNCTION dbo.fn_Split2 (@sep nvarchar(10), @s nvarchar(4000)) RETURNS table AS RETURN ( WITH Pieces(pn, start, stop) AS ( SELECT 1, 1, CHARINDEX(@sep, @s) UNION ALL SELECT pn + 1, stop + (datalength(@sep)/2), CHARINDEX(@sep, @s, stop + (datalength(@sep)/2)) FROM Pieces WHERE stop > 0 ) SELECT pn, SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 4000 END) AS s FROM Pieces ) 

Getting initials is now easy:

 DECLARE @Initials VARCHAR(8000) SELECT @Initials = COALESCE(@Initials, '') + SUBSTRING(s, 1, 1) FROM dbo.fn_Split2(' ', 'Michael Joseph Jackson') SELECT @Initials 

This returns "MJJ" if required.

Answer 6

SUBSTRING (string, startpos, endpos) AS 'Initial'