I have requirement to split column in 2 separate columns and then convert multiple date types column to standard date format column.

CREATE TABLE Report
(Id INT, Reference VARCHAR(30));

INSERT INTO Report
VALUES
(1, 'Location (11/8/22)'),
(2, 'Timesheet (11/10/22)'),
(3, 'TESTING (12/09/22)'),
(4, 'Incorrect Payment (9/10/22)'),
(5, 'Employee (11/9/22)'),
(6, 'Authorization'),
(7, 'Inactive Client'),
(8, 'Active Client (07/22/2022)'),
(9, 'TESTING (09/09/22)'),
(10, 'Timesheet')

SELECT * FROM Report

The output I need:

I was able to split the Reference column, however can't find the way to remove "(" and ")" and convert it to Standard Date format. Is it even possible to convert the way I need, because date information is from manual user input, therefore 2022-09-08 can be present in a multiple ways, such as: 09/08/2022, 09/08/22, 09/8/22, 9/8/2022 and any other possible ways.

SELECT 

   p.*

   ,SUBSTRING(p.Reference, 1, CASE CHARINDEX('(', p.Reference)
        WHEN 0
            THEN LEN(p.Reference)
        ELSE CHARINDEX('(', p.Reference) - 1
        END) AS Type

   ,SUBSTRING(p.Reference, CASE CHARINDEX('(', p.Reference)
        WHEN 0
            THEN LEN(p.Reference)   1
        ELSE CHARINDEX('(', p.Reference)   0
        END, 1000) AS Date

FROM Report AS p

CodePudding user response：

SELECT Id, Reference, 
       MAX(CAST(CASE CHARINDEX(')', value) 
                   WHEN 0 THEN  NULL
                   ELSE REPLACE(value, ')', '')
                END AS DATE)) AS TheDate
FROM   Report
       OUTER APPLY STRING_SPLIT(Reference, '(')
GROUP  BY Id, Reference;

As you can see your table violate the first normal form (1FN) by having non atomic values into a column... Then this is not a relational database but something close to CoBOL files in the old times...

Then querying is difficult and performances will be poor. You better have to normalize your DB !

CodePudding user response：

your data

declare @a table
(Id INT, Reference VARCHAR(100));

INSERT INTO @a
VALUES
(1, 'Location (11/8/22)'),
(2, 'Timesheet (11/10/22)'),
(3, 'TESTING (12/09/22)'),
(4, 'Incorrect Payment (9/10/22)'),
(5, 'Employee (11/9/22)'),
(6, 'Authorization'),
(7, 'Inactive Client'),
(8, 'Active Client (07/22/2022)'),
(9, 'TESTING (09/09/22)'),
(10, 'Timesheet')

you should use Substring and Charindex to identify () and LIKE and IIF operator to have null value for date if value don't have ()

SELECT id,
       reference,
       IIF(reference LIKE '%(%', Substring(reference, 0,
                                 Charindex('(', reference)),
       reference)  as Type,                                                     
       IIF(reference LIKE '%(%', Substring(reference, Charindex('(', reference)
                                                        1,
                                 Charindex(')', reference)
                                 - Charindex('(', reference)
                                 - 1), NULL) AS Date
FROM   @a   

dbfiddle

CodePudding user response：

;with cte as(
SELECT 
   p.id,
   p.reference

   ,SUBSTRING(p.Reference, 1, CASE CHARINDEX('(', p.Reference)
        WHEN 0
            THEN LEN(p.Reference)
        ELSE CHARINDEX('(', p.Reference) - 1
        END) AS Type

   ,REPLACE(SUBSTRING(p.Reference, CASE CHARINDEX('(', p.Reference)
        WHEN 0
            THEN LEN(p.Reference)   1
        ELSE CHARINDEX('(', p.Reference)   1
        END, 1000),')','') AS Date1

FROM #Report AS p)
select *, CASE WHEN date1 IS NULL OR date1 = '' THEN NULL ELSE CAST(date1 AS DATE) END from cte

CodePudding user response：

You could try using STRING_SPLIT function as the following

WITH split_ref As(
  SELECT R.Id, R.Reference, SP.value, SP.ordinal
  FROM Report R
  CROSS APPLY STRING_SPLIT(Reference, '(', 1) AS SP
)
SELECT Id, Reference,
       MAX(CASE ordinal WHEN 1 THEN value END) AS Type,
       MAX(CASE ordinal WHEN 2 THEN TRY_CAST(REPLACE(value, ')', '') AS DATE) END) AS [Date] 
FROM split_ref
GROUP BY Id, Reference
ORDER BY Id

See demo.