SQL Challenge "Names"-CodePudding

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

CodePudding user response：

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

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

CodePudding user response：

This may not be an answer per se, but shows some techniques where your query can be refactored to reduce duplication and increase readability.

Whenever you find yourself writing repeated expressions, you may find it useful to separate those out in a CROSS APPLY, which allows for defining and calculating expressions once and then using them many times either in final results or in additional CROSS APPLYs.

For your case, you can change the FROM clause in your main query to:

SELECT
    ...
FROM #temp AS T
CROSS APPLY (
  SELECT
    Len = LEN(T.Testname),
    SpaceCount = LEN(T.Testname) - LEN(REPLACE(Testname, ' ', '')),
    SlashIndex = CHARINDEX('/', T.Testname),
    SpaceIndex = CHARINDEX(' ', T.Testname),
    ReverseSpaceIndex = CHARINDEX(' ', REVERSE(T.Testname))
) A

and then replace earlier occurrences of the expressions with the equivalent A.Len, A.SpaceCount, etc. Your #Temp table can also be eliminated this way.

See this db<>fiddle for an updated version of your code.

You can even go further and move single use but complex calculations from the select list into an additional OUTER APPLY just to separate them out if you think it makes the code more readable. Your #test table can also be eliminated using a similar technique.

SELECT T.*, B.FirstName, B.LastName, B.FullName
INTO #test
FROM #temp AS T
CROSS APPLY (
  ...
) A
CROSS APPLY (
    SELECT -- Long complex expressions follow
        ... AS FirstName
        ,... AS LastName
        ,... AS FullName
) B
CROSS APPLY (
    SELECT ... AS ProperName
) C

See this db<>fiddle, which produces the same results and likely has the exact same execution plan.

Common Table Expressions (CTEs) can be used with similar effects.

Side note: In SQL server, the syntax SELECT Alias = expression is equivalent to SELECT expression AS Alias. The latter is standard SQL, but some find the former more readable when allowed.