sql to pick apart a string of a persons name and o

问题:

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

I want to use this UDF for generating initials for peoples names I have in the DB.

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

i am using sql2005

回答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

回答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 space in one of their names for some reason you will have an issue parsing this out, but I suspect that would be the case anyways when not storing your names in separate fields.

回答3:

Here's my solution, and it has these features/peculiarities:

• It can process however many names there are in the string. (That is, both less than two and more than three.)
• All spaces between the names are preserved.

I know the OP has specified that there can only be 2 or 3 names in his case. I don't mind. I'm just sharing a solution that works, and if it's not best for the particular problem, it's fine.

So, here's 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's a test call:

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

and the result:

----------------------------------------------------------------------------------------------------
J R   R  Tolkien

回答4:

You can achieve it via xquery as well.

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/