First character of string in SQL Server

In this post, we will see how to to fetch the first character of each word from the string and concatenate and return the new string

Example:

String: "How Are You?"

There are 3 Words in the string H-How, A-Are, Y=You? and you want to return HAY.

Just pass How Are You? to the function, it would return HAY.

CREATE FUNCTION dbo.FirstCharFromString(
    @string VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
  DECLARE @XML XML, @RESULT VARCHAR(MAX)
  SET @RESULT = ''
  SELECT  @XML = cast('<d>' + replace(@string, ' ', '</d><d>') + '</d>' as xml)
  SELECT  @RESULT = @RESULT + LEFT(T.split.value('.', 'NVARCHAR(MAX)'), 1)
  FROM    @XML.nodes('/d') T (split)
  RETURN  @RESULT
END

Now lets execute the function and check the result

SELECT dbo.FirstCharFromString('How Are You?')

OUTPUT