Quickie – Concatenate Rows as a String

Today I needed to concatenate a field from multiple rows in SQL Server to get a list of people names. Instead of doing it in code, you can use the following syntax:

DECLARE @Names VARCHAR(8000)
SELECT @name = ”
SELECT @Names = @Names + ‘,’ + Names FROM People
SELECT SUBSTRING(2, @Names, 7998)

Happy Concatenating!!

Reference

SQL Server 2005 | Add days, months and years to GetDate()

To get the current date in Sql Server queries and stored procedures, you use the GetDate() method. But what about to add a day or two to the current date, or add a month or years? To do it, you use the following query:

Add One Day

SELECT DateAdd(dd, 1, GetDate())

Add One Month Continue reading »

SQL Server | Convert Varchar to UniqueIdentifier

There are 2 ways to Convert a string to a UniqueIdentifier (GUID) in SQL Server:

SELECT CAST(’449141d9-86c9-449d-be2c-1f171b4f4567′ as UNIQUEIDENTIFIER)

SELECT CONVERT(UNIQUEIDENTIFIER, ’449141d9-86c9-449d-be2c-1f171b4f4567′)

SQL Server | Remove Special Characters

I needed to remove all the special characters from a field in a particular table in SQL Server to be used for searching purposes. After searching (binging )… I found the next SQL Server Function which did the trick.

This Function Removes any special character from the string value passed. All characters except 0-9, a-z and A-Z are all removed, whilst the remaining characters are returned back.

ALTER FUNCTION dbo.RemoveSpecialCharacters
(
@s VARCHAR(256)
)
RETURNS VARCHAR(256) WITH SCHEMABINDING

BEGIN
IF (@s is null)
RETURN null Continue reading »

SQL Server 2005 | Copy Database Diagrams

Next is the code to copy database diagrams from one database to another (tested on SQL Server 2005 and 2008)

use [DB_SOURCE_NAME]

DROP TABLE dbo.#tempsysdiagrams
–this will copy your database diagrams into a temporary table
select * into dbo.#tempsysdiagrams from sysdiagrams Continue reading »