Sql to highlight a face name string and display initials

Question

how can I get SQL to take a sting and return the first letter of every word passed into it.

I want to use this UDF to generate initials for the names of the people that I have in the database.

names can be 2 (fname, lname) or 3 (... mname) words

I am using sql2005

Answer 1

CREATE FUNCTION dbo.GetFirstLetter ( @Array VARCHAR(1000), @separator VARCHAR(10)) RETURNS @resultTable TABLE (parseValue VARCHAR(100)) AS BEGIN DECLARE @separator_position INT DECLARE @array_value VARCHAR(1000) SET @array = @array + @separator WHILE patindex('%' + @separator + '%' , @array) <> 0 BEGIN SELECT @separator_position = patindex('%' + @separator + '%', @array) SELECT @array_value = left(@array, @separator_position - 1) INSERT @resultTable VALUES (SUBSTRING(Cast(@array_value AS varchar), 1, 1)) SELECT @array = stuff(@array, 1, @separator_position, '') END RETURN END 

Answer 2

This should work for both "Firstname Lastname" and "Firstname Middlename Lastname" combinations.

 DECLARE @name AS NVARCHAR(50) SET @name = 'Firstname Middle Lastname' SELECT SUBSTRING(@name, 1, 1) + --First initial SUBSTRING(@name, CHARINDEX(' ', @name) + 1, 1) + --Middle/Last initial CASE WHEN 0 <> CHARINDEX(' ', @name, CHARINDEX(' ', @name) + 1) -- More than two words THEN SUBSTRING(@name, CHARINDEX(' ', @name, CHARINDEX(' ', @name) + 1) + 1, 1) --Last initial ELSE '' --Have to add empty string to avoid NULLing entire result END 

Of course, if users have a place in one of their names, for some reason you will have a problem with this syntax, but I suspect that it will be so if you do not store the names in different fields.

Answer 3

Here is my solution, and it has these features / features:

• It can handle, however, many names per line. (That is, less than two or more than three.)
• All spaces between names are retained.

I know that the OP indicated that in his case there can only be 2 or 3 names. I'm not against. I just use a solution that works, and if it is not suitable for a specific problem, this is normal.

So here is the function:

 CREATE FUNCTION dbo.fnGetInitials (@name varchar(max)) RETURNS varchar(max) AS BEGIN DECLARE @cutpos int, @spacepos int, @result varchar(max); DECLARE @cutlist TABLE (CutPos int, SpacePos int); SET @result = LTRIM(RTRIM(@name)); SET @cutpos = 2; SET @spacepos = CHARINDEX(' ', @result); WHILE @spacepos > 0 BEGIN INSERT INTO @cutlist VALUES (@cutpos, @spacepos); SET @spacepos = @spacepos + 1; SET @cutpos = @spacepos + 1; SET @spacepos = CHARINDEX(' ', @result, @spacepos); END; DELETE FROM @cutlist WHERE CutPos >= SpacePos; SELECT @result = STUFF(@result, CutPos, SpacePos - CutPos, '') FROM @cutlist ORDER BY CutPos DESC; RETURN @result; END; 

And here is the test call:

 SELECT dbo.fnGetInitials(' John Ronald Reuel Tolkien '); 

and the result:

 ---------------------------------------------------------------------------------------------------- JRR Tolkien 

Answer 4

You can also get it through xquery.

 Declare @Xml XML Declare @String Varchar(Max) Declare @firstletter Varchar(Max) Declare @delimiter Varchar(5) SET @delimiter=' ' SET @String= 'THIS IS SQL' 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 Stuff((SELECT '' + LEFT([Column],1)from CTE FOR XML PATH ('') ),1,0,'') 

Here is the complete solution.

http://raresql.com/2013/04/12/sql-server-get-the-first-letter-of-each-word-in-a-string-column/