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.
Order by in ASC instead of string text
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
4 answers
Sort by: Most helpful
-
Tomáš Zíka 256 Reputation points
2021-08-14T06:49:03.997+00:00 -
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).
-
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.
-
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:
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.