Valthor's DB Blog

select * from valthor.druzin

Comma delimeted string as tabular rows


Ok, so you're working with SQL Server and you've got yourself a long string with comma delimeted numbers, and desperately need to join those numbers to your table's columns. You're thinking that you'd wish that you could convert your string into a narrow table with a column and rows containing each comma seperated value.
Who hasn't had that problem, right ?

Well, here's a tiny function that turns excatly that kind of string into a excatly that kind of table ( it also works for simple strings ). It uses SQLServers XML processing to accomplish this, nothing too fancy or complicated but a really nice tool to have in your toolbox.


Have you met Merge ?

I have an ongoing love relationship with the MERGE statement.


In 2003 It was was added to the ANSI-SQL standard, and in SQL serve 2008 this was added to Micrsosfts implementation of SQL, and has been available in Oracle since version 9i.
Still, not a lot of folks use it, mostly because they've never heard of it.
What MERGE does, is to combine conditional DML statements into one statement which uses a one-pass scan on the target table being accessed.


Shadow tables

If you have an application that relies on configurational data in the database, chances are you want to track any changes made to that data, say in case something goes wrong in your application because of data changes.

Shadowing a configurational table is therefore a good idea, and this you can achieve by construcing a trigger which tracks any dml statements executed on a table, and logging each action.


Find the first uppercase letter in a string

You can never have too many string funtions, they are great when you need them.

This one gives you the index of the first uppercase letter in a string