Sort Text Date from SQL Server Table Column

Hello All;
I have a Table.Field named PageList
               select PageList from TheList
Inside of this Field, is / separated word sets, with dates on them.
File_Date_(Jan_21,_1976) / File_Date_(Mar_18,_1996) / File_Date_(Jan_22,_1983) / File_Date_(Jan_20,_1983) / File_Date_(Jan_14,_1976) / File_Date_(Jan_18,_1979) / File_Date_(Dec_18,_1979)

Open in new window


I need the output, to be like this. (They are going to be wrapped inside of a BBCode Table Schema)

70s goes here
       76 goes here
File_Date_(Jan_14,_1976)
File_Date_(Jan_21,_1976)

79 goes here
File_Date_(Jan_18,_1979)
File_Date_(Dec_18,_1979)

80s goes here
       83 goes here
File_Date_(Jan_20,_1983)
File_Date_(Jan_22,_1983)


90s goes here
       96 goes here
File_Date_(Mar_18,_1996)

Open in new window


This is going to be the default look, pretty much.
The Date will be wrapped in the parenthesis, and will also have an underscore,
and as you can see, it also has a comma, at the end of the day.

I am trying to make this as simple as possible, as we have 1,000's of pages that are going to be updated on a regular basis, and without having these ordered, and within a decent generated code structure, it is going to be very time-consuming, to say the least.

Any idea's on this one?
I know it is not going to be a simple one.
Hopefully, I have provided enough information, to get going on it.

Carrzkiss
LVL 31
Wayne BarronAuthor, Web DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Deepak ChauhanSQL Server DBACommented:
Try this...... hope this what you are looking for.

 DECLARE @seperator INT
 Declare @String nvarchar(max)
 Declare @Tab TABLE (DT   VARCHAR(100))
 set @String='File_Date_(Jan_21,_1976) / File_Date_(Mar_18,_1996) / File_Date_(Jan_22,_1983) / File_Date_(Jan_20,_1983) / File_Date_(Jan_14,_1976) / File_Date_(Jan_18,_1979) / File_Date_(Dec_18,_1979)'
 --(select PageList from TheList) 
        WHILE (CHARINDEX('/',    @String, 0) > 0)
        BEGIN             
              SET @seperator =   CHARINDEX('/',    @String, 0)      
              INSERT INTO   @Tab (DT)
              SELECT RTRIM(LTRIM(SUBSTRING(@String,   0, @seperator)))   
              SET @String = STUFF(@String,   1, @seperator,   '') 
        END
        INSERT INTO   @Tab (DT)
        SELECT RTRIM(LTRIM(@String))
        select DT from (select right(REPLACE(DT,right(DT,7),''),2)DY, right(REPLACE(DT, ')',''), 4)YR,
        * from  @Tab) A order by YR, DY

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Wayne BarronAuthor, Web DeveloperAuthor Commented:
Hello deepakChauhan;
Well, it puts them in order, however...
#1: How do I attach a select statement to it?
            select PageList from TheList where ID=1

#2: How would I separate them, and group them like I showed in the example?
PortletPaulEE Topic AdvisorCommented:
>>"I am trying to make this as simple as possible "
Then I suspect you have failed in achieving that

>>", as we have 1,000's of pages that are going to be updated"
why hold the data in such a weird way?
If you need dates as dates then do not bury them inside a string at all

even the date format you have chosen will doom you to complexity:

Jan_20,_1983
Feb_29,_2017
Mar_1,_2016

sorted by first letter that is:

F
J
M

so you have to locate each date (a potential failure point), convert it (another potential failure point) and (hopefully) sort it

I'm afraid I cannot see this as being simple (or reliable)

see: DATE and TIME ... don't be scared, and do it right (the first time)
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Wayne BarronAuthor, Web DeveloperAuthor Commented:
@Paul Maxwell
These are the name of the pages.
These are also URL...
site.com/File_Date_(Jan_14,_1976)
And then it brings you to your page.
That is the reason behind the Underscores and the abbreviated Months.
PortletPaulEE Topic AdvisorCommented:
>>"Sort Text Date from SQL Server Table Column"

OK.
19760114
1976-01-14
Etc. Are also dates
And sortable
Wayne BarronAuthor, Web DeveloperAuthor Commented:
I wanted to add this as well, before calling it a night.

In the query, we can remove the following.
From this
File_Date_(Jan_14,_1976)
to this
Jan 14, 1976

And then change it back at the end of the query.
File_Date_(Jan_14,_1976)

If that will make this easier...

Also, if so, then we will need to make it so that I can choose what the
File_Date_
is, in the form... <input>
As this part will be changing later on in the future.
The format will always be what it is.

@Paul.
Yes, that would be better, however, I can not change what has already been done.
PortletPaulEE Topic AdvisorCommented:
I would "split" those long strings into rows using a "splitter function" (there are many of these) e.g.

http://www.experts-exchange.com/articles/192/Delimited-String-Parsing-in-SQL-Server-2005-and-later.html
http://www.experts-exchange.com/questions/27646247/splitting-a-delimited-list-into-an-array-with-T-SQL.html
http://www.sqlservercentral.com/articles/Tally+Table/72993/

Use one that returns a table of rows, often this is done using CROSS APPLY and you should get rows like these

File_Date_(Jan_21,_1976) /
File_Date_(Mar_18,_1996) /
File_Date_(Jan_22,_1983) /
File_Date_(Jan_20,_1983) /
File_Date_(Jan_14,_1976) /
File_Date_(Jan_18,_1979) /
File_Date_(Dec_18,_1979)


Once the data is split and forced into rows, then you manipulate each string by replacing (for example)  'Jan' with 01, underscores with dash, comma with blank string and other unwanted detail to arrive at a string that should convert to a date

e.g.
         File_Date_(Jan_21,_1976) /
                                                               becomes
          01-21-1976
                                                               then
          TRY_CONVERT (date , '01-21-1976' , 110 )
                                                               will give you a date to filter and sort by

If you use cross apply and a table function then the structure of the code would be along these lines:

select split_string, file_date
from (
     select t.id, s.split_string, d.file_date
     from your_table as t
     cross apply (
                             dbo.splitfunction([original_string])
                           ) ([split_string]) as s
     cross appy(
                           try_convert(replace(s.split_string,'File_Date_(','') ... more
                        ) (file_date) as d
           ) as derived
where ....
order by file_date
Wayne BarronAuthor, Web DeveloperAuthor Commented:
Thanks Paul, for the information.
However, I am quite lost at the moment, for the code that you supplied.
I will read over the links, and see if i can gather some information from them as well.
Francis OmorutoInfrastructure and Database Design ConsultantCommented:
I believe the general summary of the suggestions is:
1. Create a temporary table that has File_Link (nvarchar) and File_Date (Date) columns
2. Fill the table by processing each PageList from the TheList by
2.1 Splitting the PageList using '/' (==> File_Link)
2.1.1 For each token from above, construct File_Date from each File_Link by splitting on '_' after throwing away the prefix 'File_Date_('
2.1.2 Insert File_Link and File_Date into the temporary table
3. Select and group the results from the temporary table in the format needed, using all the date grouping functions available.


Hope that has brought you back!
Wayne BarronAuthor, Web DeveloperAuthor Commented:
@Francis.
That sound sweet.
Once I re-install SQL Server 2012 (Started crashing on me, badly)
I will see if I can do what you have suggested.
PortletPaulEE Topic AdvisorCommented:
It isn't mandatory to create/use a temp table (but you can do it that way) otherwise Francis has summarized it nicely.

I would like to point out however that if the data was stored in a normalized way then 90% of this pain would disappear. In other words if your permanent table looked more like the temp table Francis has described, then the rest of the processing is quite simple (e.g. you could start at point 3  instead of starting at point 1).
Wayne BarronAuthor, Web DeveloperAuthor Commented:
Thanks for your input again Paul.
However, as I stated, it is already done and is not going to be changed.

I will jump back on this within the next couple of days, have a feel Monday and Tuesday coming up.
Wayne BarronAuthor, Web DeveloperAuthor Commented:
I finally got this working with the following.
set @String=(select PageList from TheList where id=1)

So, I am going to close this one.
As I am not going to be able to visit the task of producing what I need at the moment.
As code that was supplied by deepakChauhan will, at least, list them in order
For easier manageability.
Wayne BarronAuthor, Web DeveloperAuthor Commented:
@deepakChauhan.

I seem to have an issue with your code, running it in Classic ASP page.

This is the error
Operation is not allowed when the object is closed.
That is on the if rsSongs.eof then
And if I remove that, then I get this
Item cannot be found in the collection corresponding to the requested name or ordinal.
This is the code that I have, (Both errors are notated below.
Set sqlGetSong = Server.CreateObject("ADODB.Command")
sqlGetSong.ActiveConnection=EV_WikiConn
sqlGetSong.Prepared = true
sqlGetSong.commandtext = "DECLARE @seperator INT Declare @String nvarchar(max) Declare @Tab TABLE (DT   VARCHAR(100)) set @String = (select pagelist from songs where songtitle=?) WHILE (CHARINDEX('/', @String, 0) > 0) BEGIN SET @seperator =   CHARINDEX('/', @String, 0) INSERT INTO   @Tab (DT) SELECT RTRIM(LTRIM(SUBSTRING(@String, 0, @seperator))) SET @String = STUFF(@String, 1, @seperator, '') END SET NOCOUNT ON INSERT INTO   @Tab (DT) SELECT RTRIM(LTRIM(@String)) select DT as theFile from (select right(REPLACE(DT,right(DT,7),''),2)DY, right(REPLACE(DT, ')',''), 4)YR, * from  @Tab) A order by YR, DY"
sqlGetSong.Parameters.Append sqlGetSong.CreateParameter("@SongTitle", advarChar, adParamInput, 250, getSong) 
set rsSongs = sqlGetSong.execute EV_WikiConn.Open
if rsSongs.eof then ' Operation is not allowed when the object is closed.
' No records here
else
rsSongs("theFile") ' Item cannot be found
end if

Open in new window


How do I get the records to show from this?
Or does this take a whole different kind of asp code altogether?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.