SQL Concatenation with the STUFF function and XML PATH clause

Recently I had the necessity to retrieve a concatenated string of values as a column for a report.  This can be achieved quite simply by creating a UDF that returns the concatenated string and calling within the SELECT statement.

However, I do not like using UDF’s within SELECT statements due to performance issues.  So I started searching the net for a possible alternative and came across the following:

http://www.kodyaz.com/articles/concatenate-using-xml-path.aspx

Now I have never used the SQL STUFF function or XML Path clause but the combination of the two enabled me to achieve my goal very quickly.

The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.

You can execute SQL queries to return results as XML instead of standard rowsets. These queries can be executed directly or executed from within stored procedures and user-defined functions. To retrieve the results directly, first use the FOR XML clause of the SELECT statement.

SELECT TOP 1
  STUFF(
            (
                SELECT ', ' + Username
                FROM Users
                FOR XML PATH('')
            ), 1, 1, ''
        ) As ListOfUsers
From Users

Read rest of entry

SQL: Format Dates with DATENAME()

DATNAME() is an ANSI compliant, fast date part function built into SQL Server.

It accepts two arguments datepart and date. The datepart parameter specifies which part of the date to return - day, month, year with the function returning an nvarchar.

Syntax

DATENAME ( datepart ,date)

Example

SELECT DATENAME(m, GETDATE())

Result

January

http://msdn.microsoft.com/en-us/library/ms174395(v=sql.90).aspx

Read rest of entry

Capitalise first letters in SQL Server

SQL Server has no built in function to capitalise the first letter of each word in a string.  The function outlined below will do just that.

This function has proved very valuable!

Source: http://www.sql-server-helper.com/functions/initcap.aspx

Read rest of entry

SQL: Format Dates

Formatting dates in T-SQL is generally a nuisance as no format date function exists. In the past I have seen many developers (including myself) use the DATEPART() functions to split the dates and then manually build up a date string.

Alternatively I have seen developers CAST() the date to format.

Recently however, I discovered the different date formats that come standard in SQL Server as part of the CONVERT() function.

For an excellent thorough list of date formats please visit  http://www.sql-server-helper.com/tips/date-formats.aspx

Read rest of entry

SQL: Get the length of text, ntext & image

Ever needed to find the lengh of a ntext field in SQL only to be met with the following error message, whilst using the LEN() function?

This can be achieved by simply using the DATALENGHT() function.  DATALENGTH is especially useful with varchar, varbinary, text, image, nvarchar, and ntext data types because these data types can store variable-length data.


Note: Compatibility levels can affect return values. For more information about compatibility levels

Read rest of entry