Order by in ASC instead of string text

Kaumil Gandhi 26 Reputation points
2021-08-14T00:13:09.287+00:00

Hello

I've below table sample data, also I've provided my expected o/p. Basically I want to display notes with order by ASC with the date value for each id

CREATE TABLE #Temp
(
id smallint,
notes varchar(1000)
)

INSERT INTO #Temp VALUES ( 1, 'She is e-mailing with information to proceed with a refund. 10/15/2013' )
INSERT INTO #Temp VALUES ( 1, 'Details 5/24/12' )
INSERT INTO #Temp VALUES ( 1, 'Kims 2.18.11 Not yet registered to confirm' )

INSERT INTO #Temp VALUES ( 2, 'He is e-mailing to proceed with a payment. 5/9/2018' )
INSERT INTO #Temp VALUES ( 2, 'Details 11/22/16' )
INSERT INTO #Temp VALUES ( 2, 'Scott 12.11.15' )

INSERT INTO #Temp VALUES ( 3, 'Payment. 5/1/2015' )
INSERT INTO #Temp VALUES ( 3, 'Refund 5/1/2015' )

SELECT *
FROM #Temp

DROP TABLE #Temp

SELECT '1' AS id, 'Kims 2.18.11 Not yet registered to confirm' AS notes
UNION ALL
SELECT '1' AS id, 'Details 5/24/12' AS notes
UNION ALL
SELECT '1' AS id, 'She is e-mailing with information to proceed with a refund. 10/15/2013' AS notes
UNION ALL

SELECT '2' AS id, 'Scott 12.11.15' AS notes
UNION ALL
SELECT '2' AS id, 'Details 11/22/16' AS notes
UNION ALL
SELECT '2' AS id, 'He is e-mailing to proceed with a payment. 5/9/2018' AS notes
UNION ALL

SELECT '3' AS id, 'Payment. 5/1/2015' AS notes
UNION ALL
SELECT '3' AS id, 'Refund 5/1/2015' AS notes

Any help how to get the expected result?

Thanks in advance

Regards,
gk03

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,652 questions
{count} votes

4 answers

Sort by: Most helpful
  1. Tomáš Zíka 256 Reputation points
    2021-08-14T06:49:03.997+00:00

    If I understand this correctly the numeric part of the notes is a date and you want to order by date?
    I'm afraid you need to clean your data first - the date format is all over the place.
    When it's consistent I would recommend a separate date column - then you can sort by this date column.

    0 comments No comments

  2. Viorel 118K Reputation points
    2021-08-14T08:06:39.47+00:00

    If you already are introducing a new input field, so that the users will be able to enter the date separately, and want to migrate or support the current notes too, then check an example:

    select t.*
    from #Temp t
    cross apply (values (
        patindex('%[0-9][0-9][/.][0-9][0-9][/.][0-9][0-9][0-9][0-9]%', notes),
        patindex('%[0-9][0-9][/.][0-9][0-9][/.][0-9][0-9]%', notes),
        patindex('%[0-9][0-9][/.][0-9][/.][0-9][0-9][0-9][0-9]%', notes),
        patindex('%[0-9][0-9][/.][0-9][/.][0-9][0-9]%', notes),
    
        patindex('%[0-9][/.][0-9][0-9][/.][0-9][0-9][0-9][0-9]%', notes),
        patindex('%[0-9][/.][0-9][0-9][/.][0-9][0-9]%', notes),
        patindex('%[0-9][/.][0-9][/.][0-9][0-9][0-9][0-9]%', notes),
        patindex('%[0-9][/.][0-9][/.][0-9][0-9]%', notes)
        )) I(i1, i2, i3, i4, i5, i6, i7, i8)
    cross apply (values (
        try_convert(date, substring(notes, i1, 10), 101),
        try_convert(date, substring(notes, i2, 10), 1),
        try_convert(date, substring(notes, i3, 9), 101),
        try_convert(date, substring(notes, i4, 9), 1),
        try_convert(date, substring(notes, i5, 9), 101),
        try_convert(date, substring(notes, i6, 7), 1),
        try_convert(date, substring(notes, i7, 8), 101),
        try_convert(date, substring(notes, i8, 6), 1)
        )) D(d1, d2, d3, d4, d5, d6, d7, d8)
    order by coalesce(d1, d2, d3, d4, d5, d6, d7, d8), notes
    

    Also consider regular expressions (in C#, SQL CLR function, or in Python).

    0 comments No comments

  3. Erland Sommarskog 112.7K Reputation points MVP
    2021-08-14T08:52:42.96+00:00

    As Tom says, it would help to clean up the data.

    Just forget to do this in pure T-SQL - that would drive you insane.

    One option is to use the Regex classes in .NET to extract the dates. If you have not used the CLR in SQL Server before, there is quite a bit of a hurdle here. And your DBA would have to agree to install the assembly if you are on SQL 2017 or later.

    If you are on SQL 2017 or later, an alternative is to use Python to extract the dates. This requires that the Machine Learning extensions for Python has been installed and enabled.

    Please share the output of "SELECT @@version", so that we know more what your options are.

    0 comments No comments

  4. EchoLiu-MSFT 14,581 Reputation points
    2021-08-16T07:27:18.103+00:00

    Hi @Kaumil Gandhi ,

    Welcome to the microsoft TSQL Q&A forum!

    Please check:

    ;WITH cte as  
    (SELECT *,  
    CASE WHEN PATINDEX('%[0-9]%/[0-9]%',notes)<>0   
    THEN PATINDEX('%[0-9]%/[0-9]%',notes)  
    ELSE PATINDEX('%[0-9]%.[0-9]%',notes) END num  
    FROM #Temp)  
    ,cte2 as(SELECT *,SUBSTRING(notes,num,  
    CASE WHEN PATINDEX('%[0-9] [A-Z]%',notes)=0   
    THEN LEN(notes)-num+1  
    ELSE PATINDEX('%[0-9] [A-Z]%',notes)-num+1 END) [date]  
    FROM cte)  
      
    SELECT id,notes,TRY_CONVERT(date,[date]) [date]  
    FROM cte2  
    ORDER BY id,TRY_CONVERT(date,[date])  
    

    Output:
    123541-image.png

    If you have any question, please feel free to let me know.

    Regards
    Echo


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.