Splitting a column into multiple columns based on the text in SQL Server-CodePudding

I have a simple requirement! I receive "excess" information as a text field. It will have values like "£100 Per Year", "£225 Per Condition", "£105 per Accident" etc...I need to load the excess amount from this field into three fields of the target table.

For a policy, "P001" could have 3 different types of excess amounts like "ExcessPerYear", "ExcessPerCondition" and "ExcessPerAccident". Based on the source data I need to decide where to load the excess amount.

CREATE TABLE #SourceData
(Excess VARCHAR(200))

INSERT INTO #SourceData
VALUES
('£500 Per Year'),
('£50 Per Condition Per Year'),
('£450 Per Condition'),
('£200 Per Accident'),
('£100 Per Accident Per Year')

Select * From #SourceData

Excess
£500 Per Year
£50 Per Condition Per Year
£450 Per Condition
£200 Per Accident
£100 Per Accident Per Year

I want the output be like the following

Excess	ExcessPerYear	ExcessPerAccident	ExcessPerCondition
£500 Per Year	500	Null	Null
£50 Per Condition Per Year	Null	Null	50
£450 Per Condition	Null	Null	450
£200 Per Accident	Null	200	Null
£100 Per Accident Per Year	Null	100	Null

So basically, whenever the excess has a string like "Per Year" then the amount should be mapped to "ExcessPerYear" field. If The string has values like "Per Condition Per Year" or "Per Condition" then the excess amount should be loaded into "ExcessPerCondition" Table and finally if the text is like "Per Accident" or "Per Accident per Year" then it should be loaded into "ExcessPerAccident" field.

I have wrote the following code. It is giving me expected result, however I wanted to ask the community if there's any better way of writing the code?

I am using SQL Server 2019 Standard edition.


;WITH GetAllElements 
AS 
(
    SELECT  
            Excess
           ,PATINDEX('%[A-Z][a-z]%',Excess) AS Pos_FirstStr
           ,LEN(Excess) AS LenOfStr
           ,SUBSTRING(Excess,PATINDEX('%[A-Z][a-z]%',Excess),LEN(Excess)) AS Strng
           ,PATINDEX('%[0-9]%',Excess) AS Pos_FirstNum
           ,PATINDEX('%[^0-9]%',REPLACE(Excess,'£','')) AS Pos_LastNum
           ,TRIM(SUBSTRING(Excess,PATINDEX('%[0-9]%',Excess),PATINDEX('%[^0-9]%',REPLACE(Excess,'£','')))) AS ExcessAmount
       
    FROM #SourceData SD
)

SELECT  
       Excess
      ,CASE WHEN Strng='Per Year' THEN ExcessAmount ELSE NULL END AS ExcessPerYear
      ,CASE WHEN Strng IN ('Per Accident','Per Accident Per Year') THEN ExcessAmount ELSE NULL END AS ExcessPerAccident
      ,CASE WHEN Strng IN ('Per Condition Per Year','Per Condition') THEN ExcessAmount ELSE NULL END AS ExcessPerCondition
FROM GetAllElements

I know I don't need the CTE. I think all I need is those 3 case statements but I wrote the CTE just to make the code look clean and easy for everyone to understand.

CodePudding user response：

Just an option using a CROSS APPLY to alias the results of the CASE

Select Excess
      ,PerYear       = case when Class='PY' then value end
      ,PerAccident   = case when Class='PA' then value end
      ,PerCondidtion = case when Class='PC' then value end
 From  #SourceData A
 Cross Apply ( values ( case when charindex('Per Accident' ,Excess)>0 then 'PA'
                             when charindex('Per Condition',Excess)>0 then 'PC'
                             when charindex('Per Year'     ,Excess)>0 then 'PY' end
                        , try_convert(money,left(Excess,charindex(' ',Excess ' ')))
                      ) 
             ) B(Class,Value)

Results