SQL server string manipulation in a view... Or in XSLT

Question

I have been passed a piece of work that I can either do in my application or perhaps in SQL:

I have to get a date out of a string that may look like this:

1234567-DSP-01/01-VER-01/01

or like this:

1234567-VER-01/01-DSP-01/01

but may look like this:

00 12345 DISCH 01/01-VER-01/01 XXX X XXXXX

Yay. if it is a "DSP" then I want that date, if a "DISCH" then that date.

I am pulling the data out in a SQL Server view and would be happy to have the view transform the data for me. My application could do it but would add processor time. I could also see if the data could be manipulated before it is entered into the DB, I suppose.

Thank you for your time.

Answer 1

If you do it in the view your adding processing time on SQL which in general a more expensive resource then an app, web or some other type of client.

I'd recommend you try and format the data out when you insert the data, or you handle in the application tier. Scaling horizontally an app tier is so much easier then scalling your SQL.

Edit

I am talking the database server's physical resources are usually more expensive then a properly designed applications server's physical resources. This is because it is very easy to scale an application horizontally, it is in my opinion an order of magnitude more expensive to scale a DB server horizontally. Especially if your dealing with a transactional database and need to manage merging

I am not saying it is not possible just that scaling a database server horizontally is a much more difficult task, hence it's more expensive. The only reason I pointed this out is the OP raised a concern about using CPU cycles on the app server vs the database server. Most applications I have worked with have been data centric applications which processed through GB's of data to get a user an answer. We initially put everything on the database server because it was easier then doing it in classic asp and vb6 at the time. Over time the DB server was more and more loaded until scaling veritcally was no longer an option.

Database Servers are also designed at retrieving and joining data together. You should leave the formating of the data to the application and business rules (in general of course)

Answer 2

An option would be to check for the presence of DSP or DISCH then substring out the date as necessary.

For example (I don't have sqlserver today so I can verify syntax, sorry)

select
  date = case date_attribute
            when charindex('DSP',date_attribute) > 0 then substring(date_attribute,beg,end)
            when charindex('DISCH',date_attribute) > 0 then substring(date_attribute,beg,end)
            else 'unknown'
        end 
 from myTable

Answer 3

don't store multiple items in the same column!

store the date in its own column when inserting the row!

• add a new nullable column for the date
• write an update that pulls the date out and sets the new column
• alter the column to be not nullable
• fix your save routine to pull the date out and insert it in for you