Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

Your profile

Tools

Authors

Main Categories

Search

XML Feeds

RSS 2.0: Posts, Comments
Atom: Posts, Comments

What is RSS?

Google Ads

« SQL Server Rounding Methods

SQL Friday, The Best SQL Server Links Of The Past Week Episode 1 »

comments

The Ten Most Asked SQL Server Questions And Their Answers

by SQLDenis on Dec 10, 2008 in categories Data Modelling & Design

Instapaper

If you are active in the SQL Server newsgroups and forums as I am, you will notice that the same questions keep popping up all the time. I picked ten of them which I see daily. Since this became a pretty long blogpost I have linked all the questions below to jump to the question itself.

1) Selecting all the values from a table for a particular date
2) Search all columns in all the tables in a database for a specific value
3) Splitting string values
4) Select all rows from one table that don't exist in another table
5) Getting all rows from one table and only the latest from the child table
6) Getting all characters until a specific character
7) Return all rows with NULL values in a column
8) Row values to column (PIVOT)
9) Pad or remove leading zeroes from numbers
10) Concatenate Values From Multiple Rows Into One Column

1 Selecting all the values from a table for a particular date

This is a very popular question and people sometimes answer that you need to use between. There is a problem with between if you happen to have a value at exactly midnight. Let us take a look, first create this table.

tsql

Line number Off | Hide | Select all

CREATE TABLE SomeDates (DateColumn DATETIME)

Insert 2 values

tsql

Line number Off | Hide | Select all

INSERT INTO SomeDates VALUES('2008-10-02 00:00:00.000')
INSERT INTO SomeDates VALUES('2008-10-01 00:00:00.000')

Return everything between '2008-10-01' and '2008-10-02'

tsql

Line number Off | Hide | Select all

SELECT *
FROM SomeDates
WHERE DateColumn BETWEEN '20081001' AND '20081002'
ORDER BY DateColumn

This works without a problem, we get this returned

(results)
2008-10-01 00:00:00.000
2008-10-02 00:00:00.000

Let's add some more dates including the time portion

tsql

Line number Off | Hide | Select all

INSERT INTO SomeDates VALUES('2008-10-02 00:01:00.000')
INSERT INTO SomeDates VALUES('2008-10-02 00:00:59.000')
INSERT INTO SomeDates VALUES('2008-10-02 00:00:01.000')
INSERT INTO SomeDates VALUES('2008-10-01 00:01:00.000')
INSERT INTO SomeDates VALUES('2008-10-01 00:12:00.000')
INSERT INTO SomeDates VALUES('2008-10-01 23:00:00.000')

Return everything between '2008-10-01' and '2008-10-02'

tsql

Line number Off | Hide | Select all

SELECT *
FROM SomeDates
WHERE DateColumn BETWEEN '20081001' AND '20081002'
ORDER BY DateColumn

(results)
2008-10-01 00:00:00.000
2008-10-01 00:01:00.000
2008-10-01 00:12:00.000
2008-10-01 23:00:00.000
2008-10-02 00:00:00.000

Here is where it goes wrong; for 2008-10-02 only the midnight value is returned the other ones are ignored

Now if we change 2008-10-02 to 2008-10-03 we get what we want

tsql

Line number Off | Hide | Select all

SELECT *
FROM SomeDates
WHERE DateColumn BETWEEN '20081001' AND '20081003'
ORDER BY DateColumn

(results)
2008-10-01 00:00:00.000
2008-10-01 00:01:00.000
2008-10-01 00:12:00.000
2008-10-01 23:00:00.000
2008-10-02 00:00:00.000
2008-10-02 00:00:01.000
2008-10-02 00:00:59.000
2008-10-02 00:01:00.000

Now insert a value for 2008-10-03 (midnight)

tsql

Line number Off | Hide | Select all

INSERT INTO SomeDates VALUES('2008-10-03 00:00:00.000')

Run the query again

tsql

Line number Off | Hide | Select all

SELECT *
FROM SomeDates
WHERE DateColumn BETWEEN '20081001' AND '20081003'
ORDER BY DateColumn

We get back 2008-10-03 00:00:00.000, between will return the date if it is exactly midnight

If you use >= and < then you get exactly what you need

tsql

Line number Off | Hide | Select all

SELECT *
FROM SomeDates
WHERE DateColumn >= '20081001' AND DateColumn < '20081003'
ORDER BY DateColumn

So be careful when using between because you might get back rows that you did not expect to get back and it might mess up your reporting if you do counts or sums

Bonus: how to strip the time off a date?

To strip the time portion of a datetime, you can do this and still return a datetime

tsql

Line number Off | Hide | Select all

Select * ,DATEADD(dd, DATEDIFF(dd, 0, DateColumn), 0) as stripped
from SomeDates

you can also do a convert to varchar with a style value of 112 and then converting back to datetime

tsql

Line number Off | Hide | Select all

Select * ,convert(datetime,Convert(varchar(8),DateColumn,112)) as stripped
from SomeDates

Both methods return this
-----------------------------------------------

DateColumn              stripped
2008-10-02 00:00:00.000	2008-10-02 00:00:00.000
2008-10-01 00:00:00.000	2008-10-01 00:00:00.000
2008-10-02 00:01:00.000	2008-10-02 00:00:00.000
2008-10-02 00:00:59.000	2008-10-02 00:00:00.000
2008-10-02 00:00:01.000	2008-10-02 00:00:00.000
2008-10-01 00:01:00.000	2008-10-01 00:00:00.000
2008-10-01 00:12:00.000	2008-10-01 00:00:00.000
2008-10-01 23:00:00.000	2008-10-01 00:00:00.000
2008-10-03 00:00:00.000	2008-10-03 00:00:00.000

2 Search all columns in all the tables in a database for a specific value

How can I search all the columns in every table in my database for a specific value?
This question keeps popping up all the time. Here is a variation of this question discussed MSDN Forum T-SQL. Unfortunately there is no easy way to do this, you have to loop over all the tables in the database and search for the value.
As you can imagine if you have a huge database with a lot of tables this can take a really long time.
There are a couple of checks going on in this proc, if the data is not numeric then we skip all the numeric columns and do not search those columns. This code was created by our own gmmastros and what he did was create 3 different stored procedures, one for dates, numbers and strings. Then he created a 4th stored proc named FindMyData, this proc will only call the appropriate child procs if the datatype is right.
We only list the table name and the column name where the value is stored, we do not return that data!
First create the following 3 stored procedures

Just the date searching proc

tsql

Line number Off | Hide | Select all

CREATE PROCEDURE FindMyData_Date
@DataToFind DATETIME
AS
SET NOCOUNT ON
DECLARE @Temp TABLE(RowId INT IDENTITY(1,1), SchemaName sysname, TableName sysname, ColumnName SysName, DataType VARCHAR(100), DataFound BIT)
DECLARE @ISDATE BIT
INSERT INTO @Temp(TableName,SchemaName, ColumnName, DataType)
SELECT C.Table_Name,C.TABLE_SCHEMA, C.Column_Name, C.Data_Type
FROM Information_Schema.Columns AS C
INNER Join Information_Schema.Tables AS T
ON C.Table_Name = T.Table_Name
AND C.TABLE_SCHEMA = T.TABLE_SCHEMA
WHERE Table_Type = 'Base Table'
And (Data_Type = 'DateTime'
Or (Data_Type = 'SmallDateTime' And @DataToFind >= '19000101' And @DataToFind < '20790607'))
DECLARE @i INT
DECLARE @MAX INT
DECLARE @TableName sysname
DECLARE @ColumnName sysname
DECLARE @SchemaName sysname
DECLARE @SQL NVARCHAR(4000)
DECLARE @PARAMETERS NVARCHAR(4000)
DECLARE @DataExists BIT
DECLARE @SQLTemplate NVARCHAR(4000)
SELECT @SQLTemplate = 'If Exists(Select *
From ReplaceTableName
Where [ReplaceColumnName]
= ''' + CONVERT(VARCHAR(30), @DataToFind, 126) + '''
)
Set @DataExists = 1
Else
Set @DataExists = 0',
@PARAMETERS = '@DataExists Bit OUTPUT',
@i = 1
SELECT @i = 1, @MAX = MAX(RowId)
FROM @Temp
WHILE @i <= @MAX
BEGIN
SELECT @SQL = REPLACE(REPLACE(@SQLTemplate, 'ReplaceTableName', QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName)), 'ReplaceColumnName', ColumnName)
FROM @Temp
WHERE RowId = @i
PRINT @SQL
EXEC SP_EXECUTESQL @SQL, @PARAMETERS, @DataExists = @DataExists OUTPUT
IF @DataExists =1
UPDATE @Temp SET DataFound = 1 WHERE RowId = @i
SET @i = @i + 1
END
SELECT SchemaName,TableName, ColumnName
FROM @Temp
WHERE DataFound = 1
go

If you want to test just this proc, try this

tsql

Line number Off | Hide | Select all

exec FindMyData_Date '20070615'

This is the string proc

tsql

Line number Off | Hide | Select all

CREATE PROCEDURE FindMyData_String
@DataToFind NVARCHAR(4000),
@ExactMatch BIT = 0
AS
SET NOCOUNT ON
DECLARE @Temp TABLE(RowId INT IDENTITY(1,1), SchemaName sysname, TableName sysname, ColumnName SysName, DataType VARCHAR(100), DataFound BIT)
INSERT INTO @Temp(TableName,SchemaName, ColumnName, DataType)
SELECT C.Table_Name,C.TABLE_SCHEMA, C.Column_Name, C.Data_Type
FROM Information_Schema.Columns AS C
INNER Join Information_Schema.Tables AS T
ON C.Table_Name = T.Table_Name
AND C.TABLE_SCHEMA = T.TABLE_SCHEMA
WHERE Table_Type = 'Base Table'
And Data_Type In ('ntext','text','nvarchar','nchar','varchar','char')
DECLARE @i INT
DECLARE @MAX INT
DECLARE @TableName sysname
DECLARE @ColumnName sysname
DECLARE @SchemaName sysname
DECLARE @SQL NVARCHAR(4000)
DECLARE @PARAMETERS NVARCHAR(4000)
DECLARE @DataExists BIT
DECLARE @SQLTemplate NVARCHAR(4000)
SELECT @SQLTemplate = CASE WHEN @ExactMatch = 1
THEN 'If Exists(Select *
From ReplaceTableName
Where Convert(nVarChar(4000), [ReplaceColumnName])
= ''' + @DataToFind + '''
)
Set @DataExists = 1
Else
Set @DataExists = 0'
ELSE 'If Exists(Select *
From ReplaceTableName
Where Convert(nVarChar(4000), [ReplaceColumnName])
Like ''%' + @DataToFind + '%''
)
Set @DataExists = 1
Else
Set @DataExists = 0'
END,
@PARAMETERS = '@DataExists Bit OUTPUT',
@i = 1
SELECT @i = 1, @MAX = MAX(RowId)
FROM @Temp
WHILE @i <= @MAX
BEGIN
SELECT @SQL = REPLACE(REPLACE(@SQLTemplate, 'ReplaceTableName', QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName)), 'ReplaceColumnName', ColumnName)
FROM @Temp
WHERE RowId = @i
PRINT @SQL
EXEC SP_EXECUTESQL @SQL, @PARAMETERS, @DataExists = @DataExists OUTPUT
IF @DataExists =1
UPDATE @Temp SET DataFound = 1 WHERE RowId = @i
SET @i = @i + 1
END
SELECT SchemaName,TableName, ColumnName
FROM @Temp
WHERE DataFound = 1
GO

If you want to test just this proc, try this

tsql

Line number Off | Hide | Select all

exec FindMyData_string 'google', 0

Just the number proc

tsql

Line number Off | Hide | Select all

CREATE PROCEDURE FindMyData_Number
@DataToFind NVARCHAR(4000),
@ExactMatch BIT = 0
AS
SET NOCOUNT ON
DECLARE @Temp TABLE(RowId INT IDENTITY(1,1), SchemaName sysname, TableName sysname, ColumnName SysName, DataType VARCHAR(100), DataFound BIT)
DECLARE @IsNumber BIT
DECLARE @ISDATE BIT
IF ISNUMERIC(CONVERT(VARCHAR(20), @DataToFind)) = 1
SET @IsNumber = 1
ELSE
SET @IsNumber = 0
INSERT INTO @Temp(TableName,SchemaName, ColumnName, DataType)
SELECT C.Table_Name,C.TABLE_SCHEMA, C.Column_Name, C.Data_Type
FROM Information_Schema.Columns AS C
INNER Join Information_Schema.Tables AS T
ON C.Table_Name = T.Table_Name
AND C.TABLE_SCHEMA = T.TABLE_SCHEMA
WHERE Table_Type = 'Base Table'
And Data_Type In ('float','real','decimal','money','smallmoney','bigint','int','smallint','tinyint','bit')
DECLARE @i INT
DECLARE @MAX INT
DECLARE @TableName sysname
DECLARE @ColumnName sysname
DECLARE @SQL NVARCHAR(4000)
DECLARE @PARAMETERS NVARCHAR(4000)
DECLARE @DataExists BIT
DECLARE @SQLTemplate NVARCHAR(4000)
SELECT @SQLTemplate = CASE WHEN @ExactMatch = 1
THEN 'If Exists(Select *
From ReplaceTableName
Where Convert(VarChar(40), [ReplaceColumnName])
= ''' + @DataToFind + '''
)
Set @DataExists = 1
Else
Set @DataExists = 0'
ELSE 'If Exists(Select *
From ReplaceTableName
Where Convert(VarChar(40), [ReplaceColumnName])
Like ''%' + @DataToFind + '%''
)
Set @DataExists = 1
Else
Set @DataExists = 0'
END,
@PARAMETERS = '@DataExists Bit OUTPUT',
@i = 1
SELECT @i = 1, @MAX = MAX(RowId)
FROM @Temp
WHILE @i <= @MAX
BEGIN
SELECT @SQL = REPLACE(REPLACE(@SQLTemplate, 'ReplaceTableName', QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName)), 'ReplaceColumnName', ColumnName)
FROM @Temp
WHERE RowId = @i
PRINT @SQL
EXEC SP_EXECUTESQL @SQL, @PARAMETERS, @DataExists = @DataExists OUTPUT
IF @DataExists =1
UPDATE @Temp SET DataFound = 1 WHERE RowId = @i
SET @i = @i + 1
END
SELECT SchemaName,TableName, ColumnName
FROM @Temp
WHERE DataFound = 1
go

If you want to test just this proc, try this

tsql

Line number Off | Hide | Select all

exec FindMyData_Number '562', 1

The mother of all procs!

tsql

Line number Off | Hide | Select all

CREATE PROCEDURE FindMyData
@DataToFind NVARCHAR(4000),
@ExactMatch BIT = 0
AS
SET NOCOUNT ON
CREATE TABLE #Output(SchemaName sysname, TableName sysname, ColumnName sysname)
IF ISDATE(@DataToFind) = 1
INSERT INTO #Output EXEC FindMyData_Date @DataToFind
IF ISNUMERIC(@DataToFind) = 1
INSERT INTO #Output EXEC FindMyData_Number @DataToFind, @Exactmatch
INSERT INTO #Output EXEC FindMyData_String @DataToFind, @ExactMatch
SELECT SchemaName,TableName, ColumnName
FROM #Output
ORDER BY SchemaName,TableName, ColumnName
go

Here are some proc calls to test it out

tsql

Line number Off | Hide | Select all

exec FindMyData 'google', 0
exec FindMyData 1, 0
exec FindMyData '20081201', 0

tsql

Line number Off | Hide | Select all

exec FindMyData 'sysobjects', 0

3 Splitting string values

The fastest way to split a comma delimited string is by using a number table. If you do not have a number table in your database then use this code to create one

tsql

Line number Off | Hide | Select all

-- Create our Pivot table ** do this only once
CREATE TABLE NumberPivot (NumberID INT PRIMARY KEY)
INSERT INTO NumberPivot
SELECT number FROM master..spt_values
where type = 'P'
GO

Now you can run the following code to return each of the values in the comma delimited string in its own row

tsql

Line number Off | Hide | Select all

DECLARE @SplitString VARCHAR(1000)
SELECT @SplitString ='1,4,77,88,4546,234,2,3,54,87,9,6,4,36,6,9,9,6,4,4,68,9,0,5,3,2,'
SELECT SUBSTRING(',' + @SplitString + ',', NumberID + 1,
CHARINDEX(',', ',' + @SplitString + ',', NumberID + 1) - NumberID -1)AS VALUE
FROM NumberPivot
WHERE NumberID <= LEN(',' + @SplitString + ',') - 1
AND SUBSTRING(',' + @SplitString + ',', NumberID, 1) = ','
GO

You can also return distinct values by using DISTINCT

tsql

Line number Off | Hide | Select all

DECLARE @SplitString VARCHAR(1000)
SELECT @SplitString ='1,4,77,88,4546,234,2,3,54,87,9,6,4,36,6,9,9,6,4,4,68,9,0,5,3,2'
SELECT DISTINCT SUBSTRING(',' + @SplitString + ',', NumberID + 1,
CHARINDEX(',', ',' + @SplitString + ',', NumberID + 1) - NumberID -1)AS VALUE
FROM NumberPivot
WHERE NumberID <= LEN(',' + @SplitString + ',') - 1
AND SUBSTRING(',' + @SplitString + ',', NumberID, 1) = ','

You now can dump the result into a table and then you can join one of your real tables with that table which will execute much faster

4 Select all rows from one table that don't exist in another table

There are at least 5 ways to return data from one table which is not in another table. Two of these are SQL Server 2005 and greater only

NOT IN
NOT EXISTS
LEFT and RIGHT JOIN
OUTER APPLY (2005+)
EXCEPT (2005+)

First Create these two tables

tsql

Line number Off | Hide | Select all

CREATE TABLE testnulls (ID INT)
INSERT INTO testnulls VALUES (1)
INSERT INTO testnulls VALUES (2)
INSERT INTO testnulls VALUES (null)
CREATE TABLE testjoin (ID INT)
INSERT INTO testjoin VALUES (1)
INSERT INTO testjoin VALUES (3)

NOT IN

tsql

Line number Off | Hide | Select all

SELECT * FROM testjoin WHERE ID NOT IN(SELECT ID FROM testnulls)

What happened? Nothing gets returned! The reason is because the subquery returns a NULL and you can't compare a NULL to anything

Now run this

tsql

Line number Off | Hide | Select all

SELECT * FROM testjoin WHERE ID NOT IN(SELECT ID FROM testnulls WHERE ID IS NOT NULL)

That worked because we eliminated the NULL values in the subquery

NOT EXISTS

NOT EXISTS doesn't have the problem that NOT IN has. Run the following code

tsql

Line number Off | Hide | Select all

SELECT * FROM testjoin j
WHERE NOT EXISTS (SELECT n.ID
FROM testnulls n
WHERE n.ID = j.ID)

Everything worked as expected

LEFT and RIGHT JOIN

Plain vanilla LEFT and RIGHT JOINS

tsql

Line number Off | Hide | Select all

SELECT j.* FROM testjoin j
LEFT OUTER JOIN testnulls n ON n.ID = j.ID
WHERE n.ID IS NULL
SELECT j.* FROM testnulls n
RIGHT OUTER JOIN testjoin j ON n.ID = j.ID
WHERE n.ID IS NULL

OUTER APPLY (SQL 2005 +)

OUTER APPLY is something that got added to SQL 2005

tsql

Line number Off | Hide | Select all

SELECT j.* FROM testjoin j
OUTER APPLY
(SELECT id FROM testnulls n
WHERE n.ID = j.ID) a
WHERE a.ID IS NULL

EXCEPT(SQL 2005 +)
EXCEPT is something that got added to SQL 2005. It basically returns everything from the top table which is not in the bottom table

tsql

Line number Off | Hide | Select all

SELECT * FROM testjoin
EXCEPT
SELECT * FROM testnulls

INTERSECT
INTERSECT returns what ever is in both tables(like a regular join)

tsql

Line number Off | Hide | Select all

SELECT * FROM testjoin
INTERSECT
SELECT * FROM testnulls

5 Getting all rows from one table and only the latest from the child table

First create this table

tsql

Line number Off | Hide | Select all

CREATE TABLE #MaxVal(id INT,VALUE INT,SomeDate datetime)
INSERT #MaxVal VALUES(1,1,'20010101')
INSERT #MaxVal VALUES(1,2,'20020101')
INSERT #MaxVal VALUES(1,3,'20080101')
INSERT #MaxVal VALUES(2,1,'20010101')
INSERT #MaxVal VALUES(2,2,'20060101')
INSERT #MaxVal VALUES(2,3,'20080101')
INSERT #MaxVal VALUES(3,1,'20010101')
INSERT #MaxVal VALUES(3,2,'20080101')

If you just need the max value from a column you can just do a group by

tsql

Line number Off | Hide | Select all

SELECT id,MAX(SomeDate) AS VALUE
FROM #MaxVal
GROUP BY id

If you need the whole row back then the query below is one way of doing this

tsql

Line number Off | Hide | Select all

SELECT t.* FROM(
SELECT id,MAX(SomeDate) AS MaxValue
FROM #MaxVal
GROUP BY id) x
JOIN #MaxVal t ON x.id =t.id
AND x.MaxValue =t.SomeDate

We have another blog post on our site which has a lot more detail about doing this, as a matter of fact that blog post describes 5 ways to do it. These 5 ways are:

Key Search
1. Correlated Subquery
2. Derived Table

Number and Filter
3. Windowing Function - Two Stage
4. Windowing Function - One Stage

Simulate MS Access
5. Compound Key, aka Packed Values

That post can be found here: Including an Aggregated Column's Related Values

6 Getting all characters until a specific character (charindex + left)

There are two built in functions that you can use in SQL Server to return the first position in a column of the character you are looking for. These functions are PATINDEX and CHARINDEX. Let's take a look at how PATINDEX works. First create this table.

tsql

Line number Off | Hide | Select all

CREATE TABLE #SomeTable2
(SomeValue VARCHAR(49))
INSERT INTO #SomeTable2 VALUES ('one two three')
INSERT INTO #SomeTable2 VALUES ('1 2 3')
INSERT INTO #SomeTable2 VALUES ('abc def ghi')
INSERT INTO #SomeTable2 VALUES ('one two')
INSERT INTO #SomeTable2 VALUES ('one two three four')
INSERT INTO #SomeTable2 VALUES ('one two three four five')

Now we want to return everything up to the first space and also everything after the last space. Here is how we do that

tsql

Line number Off | Hide | Select all

select *,left(SomeValue,patindex('% %',SomeValue)-1),
right(SomeValue,patindex('% %',(reverse(SomeValue)))-1)
from #SomeTable2

(results)

one two three	one	three
1 2 3	1	3
abc def ghi	abc	ghi
one two	one	two
one two three four	one	four
one two three four five	one	five

As you can see to get the last value you can just use the REVERSE function with the RIGHT function.

7 Return all rows with NULL values in a column

People have a hard time with nulls, the first problem with NULLs is that your WHERE clause is not the same as if you would select a blank or a value

First create this simple table

tsql

Line number Off | Hide | Select all

CREATE TABLE #SomeTableNull(SomeValue VARCHAR(49))
INSERT INTO #SomeTableNull VALUES ('1')
INSERT INTO #SomeTableNull VALUES ('')
INSERT INTO #SomeTableNull VALUES ('a')
INSERT INTO #SomeTableNull VALUES (NULL)

To search a column your WHERE clause uses the = sign

tsql

Line number Off | Hide | Select all

SELECT * FROM #SomeTableNull WHERE SomeValue = ''
SELECT * FROM #SomeTableNull WHERE SomeValue = '1'
SELECT * FROM #SomeTableNull WHERE SomeValue = 'a'

Now let us try that to find a NULL value

tsql

Line number Off | Hide | Select all

SELECT * FROM #SomeTableNull WHERE SomeValue = NULL

What just happened? We got nothing back? The reason is that you cannot compare a NULL value according to ANSI standards.

tsql

Line number Off | Hide | Select all

Take a look at this
IF NULL = NULL
print 'equal'
else
print 'not so equal'

The bottom print statement got printed, NULL is unknown and you do not know if two unknows are the same

The correct way to return the NULL value is the following

tsql

Line number Off | Hide | Select all

SELECT * FROM #SomeTableNull WHERE SomeValue IS NULL

Just so that you know this, if you turn off ansi nulls then you can use =

tsql

Line number Off | Hide | Select all

SET ansi_nulls off
SELECT * FROM #SomeTableNull WHERE SomeValue = NULL
SET ansi_nulls on

However I do not recommend doing that ever, the default is ON and I would leave it like that

8 Row values to column (PIVOT)

A very frequent request is how to pivot/transpose/crosstab a query. SQL server 2005 introduced PIVOT, this makes life a lot easier compared to the SQL 2000 days. so let's see how this works. First create this table

tsql

Line number Off | Hide | Select all

CREATE TABLE #SomeTable
(SomeName VARCHAR(49), Quantity INT)

tsql

Line number Off | Hide | Select all

INSERT INTO #SomeTable VALUES ('Scarface', 2)
INSERT INTO #SomeTable VALUES ('Scarface', 4)
INSERT INTO #SomeTable VALUES ('LOTR', 5)
INSERT INTO #SomeTable VALUES ('LOTR', 6)
INSERT INTO #SomeTable VALUES ( 'Jaws', 2)
INSERT INTO #SomeTable VALUES ('Blade', 5)
INSERT INTO #SomeTable VALUES ('Saw', 6)
INSERT INTO #SomeTable VALUES ( 'Saw', 2)
INSERT INTO #SomeTable VALUES ( 'Jaws', 12)
INSERT INTO #SomeTable VALUES ('Blade', 5)
INSERT INTO #SomeTable VALUES ('Saw', 6)
INSERT INTO #SomeTable VALUES ( 'Saw', 2)

What we want is too list all the movies in a column and the sum of all quantities for that movie as a value. So in this case we want this output

Scarface	LOTR	Jaws	Saw	Blade
6	11	14	16	10

First let's look how we can do this in SQL Server 2000, this BTW will also work in SQL Server 2005/2008

tsql

Line number Off | Hide | Select all

SELECT SUM(CASE SomeName WHEN 'Scarface' THEN Quantity ELSE 0 END) AS Scarface,
SUM(CASE SomeName WHEN 'LOTR' THEN Quantity ELSE 0 END) AS LOTR,
SUM(CASE SomeName WHEN 'Jaws' THEN Quantity ELSE 0 END) AS Jaws,
SUM(CASE SomeName WHEN 'Saw' THEN Quantity ELSE 0 END) AS Saw,
SUM(CASE SomeName WHEN 'Blade' THEN Quantity ELSE 0 END) AS Blade
FROM #SomeTable

In SQL Server 2005/2008 we can use PIVOT, here is how we can use it

tsql

Line number Off | Hide | Select all

SELECT Scarface, LOTR, Jaws, Saw,Blade
FROM
(SELECT SomeName,Quantity
FROM #SomeTable) AS pivTemp
PIVOT
( SUM(Quantity)
FOR SomeName IN (Scarface, LOTR, Jaws, Saw,Blade)
) AS pivTable

That looks a little bit neater than the SQL 2000 version.
If you are interested in Column To Row take a look at Column To Row (UNPIVOT) on our wiki

9 Pad or remove leading zeroes from numbers

To pad a number with leading zeroes you use as many zeroes as you want the output to be, then use concatenation plus the right function to display it
For example if you want to pad a 6 digit column with zeroes you would do something like this
RIGHT('000000' + CONVERT(VARCHAR(6),ColumnName),6)

Here is some code that will show this, first create this table
USE tempdb
go

tsql

Line number Off | Hide | Select all

CREATE TABLE testpadding(id VARCHAR(50),id2 INT)
INSERT testpadding VALUES('000001',1)
INSERT testpadding VALUES('000134',134)
INSERT testpadding VALUES('002232',2232)
INSERT testpadding VALUES('000002',2)

Now run

tsql

Line number Off | Hide | Select all

SELECT RIGHT('000000' + CONVERT(VARCHAR(6),id2),6)
FROM testpadding

(results)
000001
000134
002232
000002

what about the id columns and stripping the zeroes from that?
No problem do this

tsql

Line number Off | Hide | Select all

SELECT CONVERT(INT,id)
FROM testpadding

(results)
1
134
2232
2

Beautiful right? Not so fast, insert this row

tsql

Line number Off | Hide | Select all

INSERT testpadding VALUES('02222222222222222222200002',2)

tsql

Line number Off | Hide | Select all

SELECT CONVERT(INT,id)
FROM testpadding

Server: Msg 248, Level 16, State 1, Line 1
The conversion of the varchar value '02222222222222222222200002' overflowed an int column.

Okay we can do a bigint instead

tsql

Line number Off | Hide | Select all

SELECT CONVERT(bigINT,id)
FROM testpadding

Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type bigint.

Nope, even that doesn't fit, now what?

tsql

Line number Off | Hide | Select all

select replace(ltrim(replace(id,'0',' ')),' ','0')
FROM testpadding

(results)
1
134
2232
2
2222222222222222222200002

So how does that work? First you replace all the zeroes with spaces, then you trim it to get rid of the leading spaces, after you replace the space back to zeroes again, the leading zeroes don't exists anymore because they were trimmed

10 Concatenate Values From Multiple Rows Into One Column

If you want to concatenate values from multiple rows into one and you want to order it then you have to use FOR XML PATH. The ORDER BY is -not- guaranteed to be processed before the concatenation occurs, if you use that technique. However, it -is- guaranteed if you use FOR XML PATH('').

Let's take a look. First create these tables

tsql

Line number Off | Hide | Select all

USE TEMPDB
GO
CREATE TABLE Authors (Id INT, LastName VARCHAR(100), FirstName VARCHAR(100))
GO
INSERT Authors VALUES(1,'Nielsen','Paul')
INSERT Authors VALUES(2,'King','Stephen')
INSERT Authors VALUES(3,'Stephenson','Neal')
GO
CREATE TABLE Books (ID INT,AuthorID INT, BookName VARCHAR(200))
GO
INSERT Books VALUES(1,1,'SQL Server 2005 Bible')
INSERT Books VALUES(2,1,'SQL Server 2008 Bible')
INSERT Books VALUES(3,2,'It')
INSERT Books VALUES(4,2,'The Stand')
INSERT Books VALUES(5,2,'Thinner')
INSERT Books VALUES(6,2,'Salems Lot')
INSERT Books VALUES(7,3,'Snow Crash')
INSERT Books VALUES(8,3,'The Diamond Age')
INSERT Books VALUES(9,3,'Cryptonomicon')
GO

This is the old style function, it will run on SQL Server 2000 and up

tsql

Line number Off | Hide | Select all

CREATE FUNCTION fnGetBooks2 (@AuthorID INT)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @BookList VARCHAR(8000)
SELECT @BookList = ''
SELECT @BookList = @BookList + BookName +','
FROM Books
WHERE AuthorID = @AuthorID
AND BookName IS NOT NULL
ORDER BY BookName --yes we can sort
RETURN LEFT(@BookList,(LEN(@BookList) -1))
END
GO

This is the same function using XML Path, so SQL Server 2005 and up

tsql

Line number Off | Hide | Select all

CREATE FUNCTION fnGetBooks (@AuthorID INT)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @BookList VARCHAR(8000)
SELECT @BookList =(
SELECT BookName + ', ' AS [text()]
FROM Books
WHERE AuthorID = @AuthorID
AND BookName IS NOT NULL
ORDER BY BookName
FOR XML PATH('') )
RETURN LEFT(@BookList,(LEN(@BookList) -1))
END
GO

Here are the calls to the functions

tsql

Line number Off | Hide | Select all

SELECT *,dbo.fnGetBooks(id) AS Books
FROM Authors

tsql

Line number Off | Hide | Select all

SELECT *,dbo.fnGetBooks2(id) AS Books
FROM Authors

(results)

Id	LastName	FirstName	Books
1	Nielsen	Paul	SQL Server 2005 Bible,SQL Server 2008 Bible
2	King	Stephen	It,Salems Lot,The Stand,Thinner
3	Stephenson	Neal	Cryptonomicon,Snow Crash,The Diamond Age

The results are the same but I have been assured by people in the private SQL Server MVP group that FOR XML PATH will preserve the order while the other function might not

In case you want some more of this stuff we have over 80 hacks, tips and trick on our SQL Server Programming Hacks wiki page

*** Remember, if you have a SQL related question try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum

About the Author

Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.

Social Sitings

Article Views: 65879 views

49 comments

Comment from: Christiaan Baes (chrissie1) [Member]

Cool, long but cool.

12/10/08 @ 10:18

Comment from: Alex Ullrich [Member]

Great post man, I don't think you made it long enough though! You could have milked this for ten posts for sure ;)

12/10/08 @ 10:44

Comment from: Impal3r [Member]

Damn, I just wore out my scroll wheel trying to read the whole thing. If I print it half the Amazon will be gone :-(

12/10/08 @ 10:46

Comment from: sirrocco [Visitor]

Great job man. Congrats.

12/11/08 @ 04:47

Comment from: Scott Swank [Visitor]

For #5 you can cut your i/o in half by using window functions.

select id, value, SomeDate
from (
select m.*,
row_number() over (
partition by id
order by SomeDate desc) as rn
from #MaxVal m
) m2
where m2.rn = 1;

12/11/08 @ 11:14

Comment from: SQLDenis [Member]

Scott,

True, thanks for the comment, my solution is slower but still 2000 compatible

12/11/08 @ 11:27

Comment from: sqlsister [Member]

You forgot - Why is my cursor so slow?

12/11/08 @ 12:23

Comment from: SQLDenis [Member]

sqlsister ,

I don't know what cursors have to do with SQL, I move them on my screen with the mouse ;-)

12/11/08 @ 12:25

Comment from: kaht [Member]

You forgot the most famous SQL question of all time (that's not really a question):

"NEED TO GO FASTER"

12/11/08 @ 14:15

Comment from: Andy [Visitor]

Another way to strip the time out of a date:

SELECT *, CONVERT(datetime,FLOOR(CONVERT(float,DateColumn,0)),0) AS stripped
FROM SomeDates

12/11/08 @ 19:31

Comment from: Jane [Visitor]

· http://jane.dallaway.com/blog

Re item 1, and specifically getting the date element without the time (prior to SQL Server 2008)

A colleague of mine discovered
SELECT CONVERT(DATETIME,FLOOR(CONVERT(FLOAT,GETDATE())))

which seems to be more efficient than converting to varchar, something I attempted to prove at TSQL - Timings.

12/12/08 @ 04:39

Comment from: Sainbayar G [Visitor] · http://sainbayar.wordpress.com

first two questions are really creative.

12/14/08 @ 23:04

Comment from: SQLSam [Visitor]

Great job, I had been looking for padding info elsewhere and came across a long winded way around it,
I knew there must have been a better way.

Also might be good to mention that when searching for columns that may include nulls, if you want to include
nulls you actually have to specify it. Took me a while to realise this.

12/23/08 @ 16:20

Comment from: Rizwan Javed [Visitor] · http://www.dubaihits.com

GREAT JOB !!!
Thank you for sharing such an amazing material, I have learn a lot from this article.

Regards,
Rizwan Javed

05/23/09 @ 01:20

Comment from: hondageoff [Member]

This is what I've been looking for. Great post!

06/11/09 @ 14:16

Comment from: Naomi [Visitor]

Can you please list all other variations of a solution to #5 problem - then I can simply point to this blog.

06/12/09 @ 06:32

Comment from: mamlin [Visitor] · http://eguanasolutions.com

Great post! For #2 (string search) you can simplify the code by replacing the CASE statement. Instead of using CASE to check for @ExactMatch and then repeating most of the code in THEN and ELSE, do a simple IF just before the select. If @ExactMatch = 1 then set @DataToFind = '%'+@DataToFind+'%'. Replace the CASE statement with just the code from inside the THEN clause and change the "=" to "LIKE". If not @ExactMatch then your LIKE compare value won't be enclosed in '%' and the operation will logically perform as an "=".

Also, I haven't put it to the test, yet, but you _may_ get better performance with a version of search that does not repeatedly call a stored procedure but instead keeps the SELECT queries internal. One such variation that's been around since 2002 is here: http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm

Again, great post! You definitely should have milked this for a full series of postings.

07/10/09 @ 16:26

Comment from: Andrew [Visitor]

You can also strip the time off a date by:

CAST(CONVERT(CHAR(10),DateColumn,101) AS DATETIME))

08/06/09 @ 09:00

Comment from: lakshmi [Visitor]

Thanks for the tips

09/03/09 @ 10:45

Comment from: Dave [Visitor]

Great page, very helpful. Just one thing re your comment about cursors....

I have a similar situation and have resolved the issue by creating an SP that just has a cursor that goes thru the Author table and concatenates all the books returning an AuthorID and a string of concatenated books.

In my main SP I create a table variable and populate it by calling the above SP. In my main query I JOIN the table variable and SELECT the concatenated books.

This has the overhead of one call to the SP with a cursor, but then removes the call to a function for each row in the main SELECT.

I know cursors are evil but function calls in SELECTS are a performance hit. Any thoughts much appreciated.

Cheers.

10/27/09 @ 16:41

Comment from: SQLDenis [Member]

Dave can you post DDL and sample DML and the code that you used...I would like to see the performance difference between the cursor and the set based operation.

10/28/09 @ 07:07

Comment from: BR [Visitor]

This comment is related to "Search all columns in all the tables in a database for a specific value" , what happens is that when your query iterates through another schema it fails , giving the below messagwe ,though the table exists in the schema in the database.
--------------------------------
Msg 208, Level 16, State 1, Line 1
Invalid object name 'def'.
------------------------------
Please can you shed some light on it as it is bit important and urgent for me .
Thanks

12/13/09 @ 11:20

Comment from: SQLDenis [Member]

BR add

AND C.TABLE_SCHEMA = T.TABLE_SCHEMA

to the JOIN, it should look like this if you have multiple schemas

SELECT C.Table_Name, C.Column_Name, C.Data_Type
FROM Information_Schema.Columns AS C
INNER Join Information_Schema.Tables AS T
ON C.Table_Name = T.Table_Name
AND C.TABLE_SCHEMA = T.TABLE_SCHEMA
WHERE Table_Type = 'Base Table'

12/13/09 @ 11:26

Comment from: BR [Visitor]

Thanks for the catch Denis,but still its not working when executing its not able to pick up the tables from various schemas.
Can you just peep in .
Thanks.

12/13/09 @ 11:58

Comment from: BR [Visitor]

I amnot able to understand as to why this is happening . Please can anyone help me out.This is bit urgent and important.
Thanks.

12/13/09 @ 12:13

Comment from: Ted Krueger (onpnt) [Member]

Hi BR,

I took the email addresses out of your last comment to protect them. Can you bring your question over to the forums in the SQL Programming section @ http://forum.lessthandot.com/viewforum.php?f=17

Thanks

onpnt

12/14/09 @ 06:24

Comment from: Roadies99 [Member]

Please can anyone helpme out on my issue.
Also have raised it acros the forum too.

12/15/09 @ 12:08

Comment from: Roadies99 [Member]

"Thanks" to each and everyone involved in helping me out to resolve the issue.
Only one request if someone and update the stored procedure on the site as it has modifications coming after its failure to run on multiple schemas.
Thanks a lot.

12/15/09 @ 12:37

Comment from: SQLDenis [Member]

I updated this post to handle schemas in 2005 and up

12/15/09 @ 12:37

Comment from: Shiv [Visitor]

hi i want the query or script how to search and replace all the the columns in a table and reaplace??

Any idea i go through some link but they didnt mention clearly how to search and replace for the single table , even i tried it shows some error
?

Msg 217, Level 16, State 1, Procedure SearchAndReplace2, Line 63
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

01/29/10 @ 06:23

Comment from: bhami [Visitor]

Excellent....it hrlped me well while searing for a value in all the columns.

06/30/10 @ 03:22

Comment from: Naomi Nosonovsky [Member]

For the number 3 how do you perform splitting if you need to split a column of comma-delimited values in SQL 2000 where CROSS APPLY is not available?

10/14/10 @ 07:27

Comment from: SQLDenis [Member]

For the number 3 how do you perform splitting if you need to split a column of comma-delimited values in SQL 2000 where CROSS APPLY is not available?

What do you mean, where do you see CROSS APPLY being used here? I have used this code in SQL 2000 since at least 2003 and never had a problem

10/14/10 @ 07:36

Comment from: Naomi Nosonovsky [Member]

I mean, if you have a table with this structure:

Id Items
1 Item1, Item2
2 Item2, Item5, Item7

and you need to change it to

1 Item1
1 Item2

etc.

how would you do this in SQL 2000 ?

10/14/10 @ 07:41

Comment from: SQLDenis [Member]

example

create table #temp(id int, col varchar(20))
insert #temp values (1,'1,2')
insert #temp values (2,'3,4')
insert #temp values (3,'4,5,6,7')

select * from #temp t
join( SELECT id,SUBSTRING(',' + col + ',', Number + 1,
CHARINDEX(',', ',' + col + ',', Number + 1) - Number -1)AS VALUE
FROM master..spt_values
cross join #temp
where type = 'P'
and Number <= LEN(',' + col + ',') - 1
AND SUBSTRING(',' + col + ',', Number, 1) = ','
) x on x.id = t.id

10/14/10 @ 09:18

Comment from: Naomi Nosonovsky [Member]

Thanks - BTW, I don't think you need an extra JOIN.

10/14/10 @ 09:50

Comment from: SQLDenis [Member]

That is correct, this will also work

SELECT #temp.*,SUBSTRING(',' + col + ',', Number + 1,
CHARINDEX(',', ',' + col + ',', Number + 1) - Number -1)AS VALUE
FROM master..spt_values
cross join #temp
where type = 'P'
and Number <= LEN(',' + col + ',') - 1
AND SUBSTRING(',' + col + ',', Number, 1) = ','

10/14/10 @ 09:52

Comment from: JCosler [Visitor]

Thanks for #4 - EXCEPT. Very clean solution and easy to quickly reverse to test table to table.

12/22/10 @ 10:25

Comment from: Erik [Member]

For Jane, Andy, and others: In my mind, using conversion to float to remove the time from a date is problematic because round-trip conversions (back to datetime) are not reliable. Since using DateDiff is even faster than converting to float, there's no reason to use float at all.

Please see Best way to remove the time portion of a datetime

And for Denis: The method given to remove leading zeroes returns the wrong answer when the value is a single '0' and the value should be treated as a number:

SELECT REPLACE(LTRIM(REPLACE('0', '0', ' ')), ' ', '0')
FROM testpadding

I suspect you already know this, though. :)

06/01/11 @ 11:24

Comment from: SQLDenis [Member]

Erik, I guess a case expression would be needed in that case to see if it is only 000...which is of course not hard to do

06/01/11 @ 11:35

Comment from: Naomi Nosonovsky [Member]

Erik,

For question 9 you may also want to check the alternative solution presented in WiKi article
Trim Leading Zeroes

May be Denis can provide a link as well.

06/01/11 @ 12:00

Comment from: Andreas [Visitor]

· http://www.itguy.gr

What an absolutely brilliant piece of work!!!

06/11/11 @ 06:41

Comment from: Geek Blogger [Visitor]

· http://www.geekblogger.org/

@Denis, Great post, i was looking for the item 2 (Search all columns in all the tables in a database for a specific value) but when i tried to execute SP to find String value i got below error.

[The module 'FindMyData_String' depends on the missing object 'SP_EXECUTESQL'. The module will still be created; however, it cannot run successfully until the object exists.]

Can you please guide me how to resolve this issue so that i can execute it successfully and find the string without any error.

07/27/11 @ 09:12

Comment from: SQLDenis [Member]

The formatter we use on this site makes all keywords uppercase and will also do it with extended procedures

Can you try lowercase, instead of SP_EXECUTESQL use sp_executesql, maybe your database collation is case sensitive?

07/27/11 @ 09:42

Comment from: Naomi Nosonovsky [Member]

Hi Geek Blogger,

Also you may try an alternative solution (although similar idea):

How to search a string value in all columns in the table

07/27/11 @ 10:17

Comment from: Scott [Visitor]

Thanks so very much for this post. I am an Oracle PL/SQL developer now working with SQL Server 2008 and this post had exactly what I was looking to do with searching every column in every table (Number 2) and it works perfectly in SQL Server 2008. I love the fact you allow for exact or like matching. And the other questions I am sure I will need to look at later.

Scott

08/19/11 @ 14:36

Comment from: Shri [Visitor]

The procedure for 'Search all columns in all the tables in a database for a specific value' helped me a lot. Thank you so much

02/02/12 @ 19:27

Comment from: matheusfilipe [Member]

Man, that tips save my life!

09/11/12 @ 15:07

Comment from: Reto Egeter [Visitor]

· http://fullparam.wordpress.com

I've revisited the topic of "Search All Tables" and added some bells and whistles (Also search XML) - and it's not in a stored procedure format:
http://fullparam.wordpress.com/2012/09/07/fck-it-i-am-going-to-search-all-tables-all-collumns/

11/27/12 @ 08:57

Comment feed for this post

LessThanDot

Your profile

Tools

Tag cloud

Authors

Main Categories

Search

XML Feeds

Google Ads

The Ten Most Asked SQL Server Questions And Their Answers

1 Selecting all the values from a table for a particular date

2 Search all columns in all the tables in a database for a specific value

3 Splitting string values

4 Select all rows from one table that don't exist in another table

5 Getting all rows from one table and only the latest from the child table

6 Getting all characters until a specific character (charindex + left)

7 Return all rows with NULL values in a column

8 Row values to column (PIVOT)

9 Pad or remove leading zeroes from numbers

10 Concatenate Values From Multiple Rows Into One Column

About the Author

49 comments

Leave a comment

LessThanDot

LTD Social Sitings

Your profile

Tools

Tag cloud

Authors

Main Categories

Search

XML Feeds

Google Ads

The Ten Most Asked SQL Server Questions And Their Answers

1 Selecting all the values from a table for a particular date

2 Search all columns in all the tables in a database for a specific value

3 Splitting string values

4 Select all rows from one table that don't exist in another table

5 Getting all rows from one table and only the latest from the child table

6 Getting all characters until a specific character (charindex + left)

7 Return all rows with NULL values in a column

8 Row values to column (PIVOT)

9 Pad or remove leading zeroes from numbers

10 Concatenate Values From Multiple Rows Into One Column

About the Author

49 comments

Leave a comment