Home > Net >  Split column, and then convert different date types to standard date format (YYYY-MM-DD) - SQL Serve
Split column, and then convert different date types to standard date format (YYYY-MM-DD) - SQL Serve

Time:12-15

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
Id Reference
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

The output I need:

Id Reference Type Date
1 Location (11/8/22) Location 2022-11-08
2 Timesheet (11/10/22) Timesheet 2022-11-10
3 TESTING (12/09/22) TESTING 2022-12-09
4 Incorrect Payment (9/10/22) Incorrect Payment 2022-09-10
5 Employee (11/9/22) Employee 2022-11-09
6 Authorization Authorization NULL
7 Inactive Client Inactive Client NULL
8 Active Client (07/22/2022) Active Client 2022-07-22
9 TESTING (09/09/22) TESTING 2022-09-09
10 Timesheet Timesheet NULL

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
Id Reference Type Date
1 Location (11/8/22) Location (11/8/22)
2 Timesheet (11/10/22) Timesheet (11/10/22)
3 TESTING (12/09/22) TESTING (12/09/22)
4 Incorrect Payment (9/10/22) Incorrect Payment (9/10/22)
5 Employee (11/9/22) Employee (11/9/22)
6 Authorization Authorization
7 Inactive Client Inactive Client
8 Active Client (07/22/2022) Active Client (07/22/2022)
9 TESTING (09/09/22) TESTING (09/09/22)
10 Timesheet Timesheet

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.

  • Related