Order by in ASC instead of string text

Question

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

Answer 1

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:

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.

Answer 2

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.

Answer 3

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).

Answer 4

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.