Get Name with initial from full name using sql server-CodePudding

I hope to get the name with the initial from the client's full name using the SQL server. The format should be the First letter of the first name and middle names except last name Last name

Eg: If the full name is John Cena, then the output should be J Cena

if the full name is Wathsala Malshani Perera, then the output should be W M Perera.

please help me to build a query. Thank you

CodePudding user response：

SELECT LEFT(FirstName, 1), LastName FROM Table

Left(column, 1) will return only the first letter for a column. If you have a middle name it should go like this

SELECT LEFT(FirstName, 1), LEFT(MiddleName, 1), LastName FROM Table

You could also use SUBSTRING(FirstName, 1, 1), which is equivalent to the usage of Left I've shown

CodePudding user response：

Hello I'm checking your request, and I think the best way should be you to use a stored procedure, I will not address this topic in this answer, but I leave an example algorithm to insert in the store procedure

DECLARE @name as nvarchar(50)
DECLARE @nSpace as int
DECLARE @i as int
DECLARE @nameResult as nvarchar(50)
DECLARE @index as int
DECLARE @newname as nvarchar(50)
--you can replace the name for test if all works well
--that variable @name you need to pass in store procedure
SET @name = 'John Cena'
--get the number of the spaces in the string
SET @nSpace = LEN(@name)-LEN(REPLACE(@name, ' ', ''))
--check if the number the spaces is even or odd, if is even need add a space at the end
if @nSpace % 2 = 0 
    SET @name = @name   ' '

--SELECT @nSpace
--index used in the loop
SET @i = 1
--inicialization the variable nameresult
SET @nameResult = ''

while @i <= @nSpace
begin
    --get the index of the position of the space
    SET @index = CHARINDEX(' ',@name)
    --get the first letter of the substring
    SET @newname = SUBSTRING(@name,1,@index)
    --start to mount the name
    SET @nameResult = @nameResult   LEFT(@newname,1)   ' '
    --remove from the name the part already usend
    SET @name = RIGHT(@name,len(@name)-(@index-1))
    --increase the loop
    SET @i = @i   1

end
--finish with the last name
SET @nameResult = @nameResult   @name
--show the result, in the store procedure that should be the return of the function
SELECT @nameResult

Best Regards