Substring in T-SQL-CodePudding

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

CodePudding user response：

You can count short names with a window function

select  [Full Name]
  , case when count(*) over(partition by Short_Name) >1 then [Full Name] else Short_Name end Short_Name
from(
    SELECT 
        FName, FName   ' '   LName [Full Name],
        SUBSTRING(LTRIM(RTRIM(FName)), 1, 1)   '. '   LTRIM(RTRIM(LName)) Short_Name
    FROM 
        testMe
) t

CodePudding user response：

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.

CodePudding user response：

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