Friday, August 22, 2014
   
Text Size
Login

T-SQL functions to convert Strings to Tables

Applies to:
  • Microsoft SQL Server 2008 R2
  • Microsoft SQL Server 2012
What?
These were in a solution and I thought I'd note them on my site so I can refer to them more easily.

How?

Include a carriage return in a column heading

Applies To:
  • MS SQL Server 2008 R2
  • MS Windows 7 Enterprise (Client)
  • MS Excel 2010

What?
A really quick note on how to insert a carriage return or new line into the column name/alias (the header). It might seem trivial but these little aesthetic changes done at the database level can save some time.

Why?
I have an Excel report which dynamically gets its content from a data source located on a database on the other side of the world. I want the header in the column "Academic Week" to break across two lines so that the column doesn't expand to the width of "Academic Week" and instead expands to the width of the word "Academic".

What I have:

  1.  Academic Week    Monday      Tuesday     Wednesday   Thursday    Friday 
  2.  ---------------- ----------- ----------- ----------- ----------- ----------- 
  3.  1                14-Jul-2014 15-Jul-2014 16-Jul-2014 17-Jul-2014 18-Jul-2014 
  4.  2                21-Jul-2014 22-Jul-2014 23-Jul-2014 24-Jul-2014 25-Jul-2014 
  5.  ... 
What I want:

  1.  Academic  
  2.  Week      Monday      Tuesday     Wednesday   Thursday    Friday 
  3.  --------- ----------- ----------- ----------- ----------- ----------- 
  4.  1         14-Jul-2014 15-Jul-2014 16-Jul-2014 17-Jul-2014 18-Jul-2014 
  5.  2         21-Jul-2014 22-Jul-2014 23-Jul-2014 24-Jul-2014 25-Jul-2014 
  6.  ... 


How?
To do this in a select query resultset, you insert the special character references "CHAR(10)" [line feed] and "CHAR(13)" [carriage return] but to do this in the name of the column heading, the answer is a much simpler one,

Convert to Proper Case in T-SQL

What?
So this is an article exploring how to convert UPPERCASE text into mixed case. The feed is originally for a personnel feed so it won't be converting long paragraphs of English text. Instead it will be applied to names and addresses as well as job titles and departments.

Why?
We wanted a T-SQL version despite having successfully built a custom script component for SSIS.

How?
You can search my site for how to do this in VB or C#. This version is entirely using T-SQL and we're going to use a function so no dynamic SQL (execute, evaluate).

T-SQL: Parse an XML value

What?
This took me a while to find so I've posted an article below detailing how to parse or extract values from a string containing XML code.

Why?
I'm working with a system which stores XML strings in a database and rather than a separate file, it stores these in a row.

How?

Compare two databases using T-SQL

Applies to:
  • MS SQL Server Management Studio 2008
  • Two Transact-SQL databases
What?
A quick article on how to compare two Microsoft databases using the tools provided with SQL Server and without having to download any third-party products.

Why?
I googled and binged and all I could find were people selling third party products... they don't get it. If you are reading this, it's likely you've paid for a commercial version of a Microsoft product which cannot possibly be cheap. MS SQL Server Management Studio (SSMS) comes included so use it...

How?
Well I started with the method #1 listed here and then just built on this.

Latest Posts

  • Joes Word Cloud (JWC)

    • Wed 20-Aug-14
      Salut Nathalie! I could only access the under construction page of your website, so I'm not 100% sure.
      Webmaster  
    • Tue 19-Aug-14
      Hi Joe, Thank you for your prompt reply. Below, an "image" of the cloud on my website : "pour ante ...
      Nathalie  
    • Mon 18-Aug-14
      Hi Nathalie, Thanks for the message. The module does not see Virtuemart text but that sounds like ...
      Webmaster  
    • Mon 18-Aug-14
      Hi Joel, Fist of all, thank you for the module. What such a work ! I have a problem with Virtuemart module.
      Nathalie  
    • Mon 18-Aug-14
      Hi there, glad to hear it's working. Yes, the exclude list needs to be words separated by commas ...
      Webmaster