Search code examples
sqlsql-servert-sqlsql-server-2016

SQL Challenge "Names"

Setup:

Below is a section of code that generates a table of sample names in an unusual set of formats. The task is to convert them into a standard format. The also list the desired result for each name so there is no confusion on the request.

DROP TABLE IF EXISTS #temp;
CREATE TABLE #temp (Testname VARCHAR(20) null, Desiredresult VARCHAR(20) null);
INSERT INTO #temp(Testname, Desiredresult)
VALUES('ct last/firstn bc', 'Firstn Last');
INSERT INTO #temp(Testname, Desiredresult)
VALUES('ct lastn/first', 'First Lastn');
INSERT INTO #temp(Testname, Desiredresult)
VALUES('last/firstname bs', 'Firstname Last');
INSERT INTO #temp(Testname, Desiredresult)
VALUES('lastname/first', 'First Lastname');
INSERT INTO #temp(Testname, Desiredresult)
VALUES('First Last', 'First Last');
INSERT INTO #temp(Testname, Desiredresult)
VALUES('Firstname A Lastname', 'Firstname Lastname');

I was able to generate code that works for this but I have no doubt it is not the most efficient method of doing this. I am curious to know a better approach to this task. Below is the code I wrote for this.

DROP TABLE IF EXISTS #test
SELECT *
,CASE
    WHEN LEN(Testname)-LEN(REPLACE(Testname, ' ', '')) = 2 AND CHARINDEX('/',T.Testname) <> 0 THEN SUBSTRING(T.Testname,CHARINDEX('/',T.Testname)+1,LEN(T.Testname)-CHARINDEX('/',T.Testname)+1-CHARINDEX(' ',REVERSE(T.Testname))-1) 
    WHEN LEN(Testname)-LEN(REPLACE(Testname, ' ', '')) = 2 AND CHARINDEX('/',T.Testname) =  0 THEN LEFT(T.Testname,CHARINDEX(' ',T.Testname)-1) 
    WHEN LEN(Testname)-LEN(REPLACE(Testname, ' ', '')) = 1 AND CHARINDEX('/',T.Testname) <> 0 AND CHARINDEX(' ',T.Testname) < CHARINDEX('/',T.TestName) THEN SUBSTRING(T.Testname,CHARINDEX('/',T.Testname)+1,LEN(T.Testname)) 
    WHEN LEN(Testname)-LEN(REPLACE(Testname, ' ', '')) = 1 AND CHARINDEX('/',T.Testname) <> 0 AND CHARINDEX(' ',T.Testname) > CHARINDEX('/',T.TestName)THEN SUBSTRING(T.Testname,CHARINDEX('/',T.Testname)+1,CHARINDEX(' ',T.Testname)-CHARINDEX('/',T.Testname)-1) 
    WHEN LEN(Testname)-LEN(REPLACE(Testname, ' ', '')) = 1 AND CHARINDEX('/',T.Testname) =  0 THEN LEFT(T.Testname,CHARINDEX(' ',T.Testname)-1)
    WHEN LEN(Testname)-LEN(REPLACE(Testname, ' ', '')) = 0 THEN SUBSTRING(T.Testname,CHARINDEX('/',T.Testname)+1,LEN(T.Testname)) 
END AS FirstName
,CASE
    WHEN LEN(Testname)-LEN(REPLACE(Testname, ' ', '')) = 2 AND CHARINDEX('/',T.Testname) <> 0 THEN SUBSTRING(T.Testname,CHARINDEX(' ',T.Testname)+1,CHARINDEX('/',T.Testname)-CHARINDEX(' ',T.Testname)-1)
    WHEN LEN(Testname)-LEN(REPLACE(Testname, ' ', '')) = 2 AND CHARINDEX('/',T.Testname) =  0 THEN RIGHT(T.Testname,CHARINDEX(' ',REVERSE(T.Testname))-1)
    WHEN LEN(Testname)-LEN(REPLACE(Testname, ' ', '')) = 1 AND CHARINDEX('/',T.Testname) <> 0 AND CHARINDEX(' ',T.Testname) < CHARINDEX('/',T.TestName) THEN SUBSTRING(T.Testname,CHARINDEX(' ',T.Testname)+1,CHARINDEX('/',T.Testname)-CHARINDEX(' ',T.Testname)-1)
    WHEN LEN(Testname)-LEN(REPLACE(Testname, ' ', '')) = 1 AND CHARINDEX('/',T.Testname) <> 0 AND CHARINDEX(' ',T.Testname) > CHARINDEX('/',T.TestName)THEN SUBSTRING(T.Testname,1,CHARINDEX('/',T.Testname)-1)
    WHEN LEN(Testname)-LEN(REPLACE(Testname, ' ', '')) = 1 AND CHARINDEX('/',T.Testname) =  0 THEN RIGHT(T.Testname,CHARINDEX(' ',REVERSE(T.Testname))-1)
    WHEN LEN(Testname)-LEN(REPLACE(Testname, ' ', '')) = 0 THEN SUBSTRING(T.Testname,1,CHARINDEX('/',T.Testname)-1)
END AS LastName
,CASE
    WHEN LEN(Testname)-LEN(REPLACE(Testname, ' ', '')) = 2 AND CHARINDEX('/',T.Testname) <> 0 THEN SUBSTRING(T.Testname,CHARINDEX('/',T.Testname)+1,LEN(T.Testname)-CHARINDEX('/',T.Testname)+1-CHARINDEX(' ',REVERSE(T.Testname))) + SUBSTRING(T.Testname,CHARINDEX(' ',T.Testname)+1,CHARINDEX('/',T.Testname)-CHARINDEX(' ',T.Testname)-1)
    WHEN LEN(Testname)-LEN(REPLACE(Testname, ' ', '')) = 2 AND CHARINDEX('/',T.Testname) =  0 THEN LEFT(T.Testname,CHARINDEX(' ',T.Testname)-1) + RIGHT(T.Testname,CHARINDEX(' ',REVERSE(T.Testname)))
    WHEN LEN(Testname)-LEN(REPLACE(Testname, ' ', '')) = 1 AND CHARINDEX('/',T.Testname) <> 0 AND CHARINDEX(' ',T.Testname) < CHARINDEX('/',T.TestName) THEN SUBSTRING(T.Testname,CHARINDEX('/',T.Testname)+1,LEN(T.Testname)) + ' ' + SUBSTRING(T.Testname,CHARINDEX(' ',T.Testname)+1,CHARINDEX('/',T.Testname)-CHARINDEX(' ',T.Testname)-1)
    WHEN LEN(Testname)-LEN(REPLACE(Testname, ' ', '')) = 1 AND CHARINDEX('/',T.Testname) <> 0 AND CHARINDEX(' ',T.Testname) > CHARINDEX('/',T.TestName)THEN SUBSTRING(T.Testname,CHARINDEX('/',T.Testname)+1,CHARINDEX(' ',T.Testname)-CHARINDEX('/',T.Testname)-1) + ' ' + SUBSTRING(T.Testname,1,CHARINDEX('/',T.Testname)-1)
    WHEN LEN(Testname)-LEN(REPLACE(Testname, ' ', '')) = 1 AND CHARINDEX('/',T.Testname) =  0 THEN T.Testname
    WHEN LEN(Testname)-LEN(REPLACE(Testname, ' ', '')) = 0 THEN SUBSTRING(T.Testname,CHARINDEX('/',T.Testname)+1,LEN(T.Testname)) + ' ' + SUBSTRING(T.Testname,1,CHARINDEX('/',T.Testname)-1)
END AS FullName
INTO #test
FROM #temp AS T

SELECT 
     T.Testname
    ,T.Desiredresult
    ,UPPER(LEFT(T.FirstName,1))+LOWER(RIGHT(T.FirstName,LEN(T.FirstName)-1))+' '+UPPER(LEFT(T.LastName,1))+LOWER(RIGHT(T.LastName,LEN(T.LastName)-1)) AS ProperName

FROM #test AS T
Solution

  • Here's an alternative which may be a little cleaner for the limited challenge

    Example

    Select A.* 
      ,DispName = case when charindex('/',TestName)>0 
                       then ltrim(concat(Pos4,' '+Pos3,' '+Pos2,' '+Pos1))
                       else ltrim(concat(Pos1,' '+Pos2,' '+Pos3,' '+Pos4))
                  end
 From  #Temp A
 Cross Apply (
                Select Pos1 = JSON_VALUE(S,'$[0]')+case when len(JSON_VALUE(S,'$[0]'))<=2 then null else '' end
                      ,Pos2 = JSON_VALUE(S,'$[1]')+case when len(JSON_VALUE(S,'$[1]'))<=2 then null else '' end
                      ,Pos3 = JSON_VALUE(S,'$[2]')+case when len(JSON_VALUE(S,'$[2]'))<=2 then null else '' end
                      ,Pos4 = JSON_VALUE(S,'$[3]')+case when len(JSON_VALUE(S,'$[3]'))<=2 then null else '' end
                 From (values ( '["'+replace(string_escape(replace(TestName,'/',' '),'json'),' ','","')+'"]' ) ) B1(S)  
             ) B

    Results

    enter image description here

    Extended for The ProperCase

    Select A.* 
      ,DispName = case when charindex('/',TestName)>0 
                       then ltrim(concat(Pos4,' '+Pos3,' '+Pos2,' '+Pos1))
                       else ltrim(concat(Pos1,' '+Pos2,' '+Pos3,' '+Pos4))
                  end
 From  #Temp A
 Cross Apply (
                Select Pos1 = upper(left(Pos1,1))+lower(stuff(Pos1,1,1,''))+case when len(Pos1)<=2 then null else '' end
                      ,Pos2 = upper(left(Pos2,1))+lower(stuff(Pos2,1,1,''))+case when len(Pos2)<=2 then null else '' end
                      ,Pos3 = upper(left(Pos3,1))+lower(stuff(Pos3,1,1,''))+case when len(Pos3)<=2 then null else '' end
                      ,Pos4 = upper(left(Pos4,1))+lower(stuff(Pos4,1,1,''))+case when len(Pos4)<=2 then null else '' end
                  From (
                        Select Pos1 = JSON_VALUE(S,'$[0]')
                              ,Pos2 = JSON_VALUE(S,'$[1]')
                              ,Pos3 = JSON_VALUE(S,'$[2]')
                              ,Pos4 = JSON_VALUE(S,'$[3]')
                         From (values ( '["'+replace(string_escape(replace(TestName,'/',' '),'json'),' ','","')+'"]' ) ) B1(S)  
                        ) B0
             ) B

    enter image description here