'Substring in T-SQL

I want to Change the full name if the first letter of the first name and last name are both same

This is my code:

SELECT 
    Id, FName + ' ' + LName 'Full Name',
    SUBSTRING(LTRIM(RTRIM(FName)), 1, 1) + '. ' + LTRIM(RTRIM(LName)) 'Short Name'
FROM 
    testMe

Output:

    Full Name       Short Name      Desired Result
1   John Smith      J. Smith          John Smith 
2   Jack Smith      J. Smith          Jack Smith
3   Jule Smith      J. Smith          Jule Smith
4   Paul Smith      P. Smith          P. Smith  
5   Steve Mark      S. Mark           S. Mark
6   Ashley  Howard  A. Howard         Ashley Howard
7   Adam Campbell   A. Campbell       Adam Campbell
8   Alex Campbell   A. Campbell       Alex Campbell

Screenshot for illustration:_

enter image description here

sql-servertsql


Solution 1:[1]

I think I know what you need. If I guessed correctly... Try this:

 DECLARE        @Name   TABLE (ID int, FName varchar(50), LName varchar(50))
 INSERT INTO    @Name   VALUES
                (1, 'John'  ,   'Smith'   )
            ,   (2, 'Jack'  ,   'Smith'   )
            ,   (3, 'Jule'  ,   'Smith'   )
            ,   (4, 'Paul'  ,   'Smith'   )
            ,   (5, 'Steve' ,   'Mark'    )
            ,   (6, 'Ashley',   'Howard'  )
            ,   (7, 'Adam'  ,   'Campbell')
            ,   (8, 'Alex'  ,   'Campbell')
;

WITH    DupFNameFirst   AS
    (
        SELECT
                FName   =   MAX(FName)
            ,   Qty     =   SUM(1)
        FROM    @Name
        GROUP BY LEFT(FName, 1) + LName
    )
,   DupLName    AS
    (
        SELECT
                LName   
            ,   Qty =   SUM(1)
        FROM    @Name
        GROUP BY LName
    )
,       FinalName   AS
    (
        SELECT
                ID
            ,   FullName    =   FName           + ' '   + LName
            ,   ShortName   =   LEFT(FName,1)   + '. '  + LName
            ,   FName
            ,   LName
        FROM    @Name
    )

        SELECT
                    ID
                ,   FullName    
                ,   ShortName   
                ,   DesiredName =   CASE WHEN F.Qty = 1 OR D.Qty = 1 THEN N.ShortName ELSE N.FullName END
        FROM        FinalName       N
        LEFT  JOIN  DupFNameFirst   F   ON  F.FName = N.FName
        INNER JOIN  DupLName        D   ON  D.LName = N.LName
        ORDER BY ID

Solution 2:[2]

Hard to tell exactly what you need, but maybe a CASE statement would help:

SELECT  
    CASE
        WHEN LEFT(LTRIM(RTRIM(FName)), 1) = LEFT(LTRIM(RTRIM(LName)), 1 ) THEN SUBSTRING(LTRIM(RTRIM(FName)), 1, 1) + '. ' + LTRIM(RTRIM(LName))
        ELSE FName + ' ' + LName
    END As FullName

The first CASE condition is when the first letter of first name = first letter of last name. 2nd condition is when they do not match.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Andy3B
Solution 2 DanielG

Related Questions