Working with comma separated strings in t-SQL

Problem

When working with t-SQL you might run into a scenario where you want to return all the records that corresponds to the IDs found in a comma separated list, e.g. ‘123,456,789’. There are several ways to solve this problem and while browsing around I found an interesting solution.

Solution

In the example below I take advantage of the CHARINDEX and PATINDEX functions in t-SQL to search for specific patterns in the comma-separated string by concatenating the customer ID with commas and then match the concatenated string with the id string. The functions returns a number  greater than 0 if the sought string exists in the ID string which can be useful in my scenario where I got a comma separated list with customer IDs.

CHARINDEX works similar to the Substring or InStr functions that you can find in other programming languages. The function takes three parameters and the first is the string to search for, the second is the string to search and the third (which is optional) is the starting point where the search will begin in the string to search.

PATINDEX works similar to CHARINDEX, but allows the use of wildcards when performing the search. The function takes two parameters and the first is the string or pattern to search for and the second is the column or variable that will be searched. The method returns an integer bigger.

The example below also show that LIKE can be used to search the comma-separated string for specific IDs using a concatenated value along with wildcards.

USE [master]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE DATABASE [WeirdScience]
GO

USE [WeirdScience]
GO

CREATE TABLE [dbo].[Customers](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Customer] [nvarchar](50) NOT NULL
) ON [PRIMARY]
GO

INSERT Customers (Customer) VALUES (N'Dan Jansson')
INSERT Customers (Customer) VALUES (N'Lars Svensson')
INSERT Customers (Customer) VALUES (N'Lisa Karlsson')
INSERT Customers (Customer) VALUES (N'Kalle Johansson')
INSERT Customers (Customer) VALUES (N'Gullan Bengtsson')
GO

DECLARE @customerstring varchar(max);
SET @customerstring = '1,3,4,6'

-------------------------------
-- This example is based on the following article:
-- http://www.projectdmx.com/tsql/sqlarrays.aspx
-------------------------------

SELECT *
  FROM Customers
 WHERE CHARINDEX(
	',' + CAST(ID AS NVARCHAR(50)) + ',', 
	',' + @customerstring + ','
	) > 0
 
SELECT *
  FROM Customers 
 WHERE PATINDEX(
	'%,' + CAST(ID AS NVARCHAR(50)) + ',%', 
	',' + @customerstring + ','
	) > 0

SELECT *
  FROM Customers
 WHERE ',' + @customerstring + ',' 
  LIKE '%,' + CAST(ID AS NVARCHAR(50)) + ',%'

USE master
GO

DROP DATABASE WeirdScience
GO

For more ideas on how to work with arrays in SQL Server you should take a look at the excellent article found here: http://www.projectdmx.com/tsql/sqlarrays.aspx

Advertisements

Shrinking the Transaction Log in SQL Server

Problem

Well, this is a classical problem: The transaction log has been growing since who knows when and is now taking up almost all space on the server. You need to shrink the transaction log in order to free space on the server.

Solution

There are several solutions to this problem and it all depends on your database. This time I used the following script to get rid of all data in the transaction log and worth mentioning is that I didn’t care about the data in the transaction log; my goal was only to get rid of the unnecessary data and to reduce the size of the transaction log.

USE [MyDatabase]  
GO
ALTER DATABASE [MyDatabase]
SET RECOVERY SIMPLE;
GO
DBCC SHRINKFILE ([MyDatabase_Log], 1);
GO
ALTER DATABASE [MyDatabase]
SET RECOVERY FULL;
GO

The script above worked fine for me and you will find similar solutions on the internet. If you analyze the script you will see that it changes the recovery model from FULL to SIMPLE which means that the database will not longer log transactions that occur in the database. After the change we execute the DBCC SHRINKFILE command that will try to shrink the file stated by the logical filename (MyDatabase_Log) to the desired size in megabytes (the second parameter to the DBCC command). When the file has been shrunk we restore the recovery model to FULL that was the original setting for the database; of course this all depends on your scenario, but my guess if you read this is that you are using the FULL recovery model. 

While looking around I found another older solution that were used earlier to accomplish sort of the same thing, however it’s recommended not to use the approach mentioned below.

USE [MyDatabase]  
GO
DBCC SHRINKFILE([MyDatabase_Log], 1)
BACKUP LOG [MyDatabase] WITH TRUNCATE_ONLY
DBCC SHRINKFILE([MyDatabase_Log], 1)
GO