Congratulations to Narasimhan Jayachandran, a database management consultant for HTC Global Services in Troy, Michigan, and Shannon L. Edie, senior database analyst for Kaiser Permanente Center for Health Research in Portland, Oregon. Narasimhan won first prize of $100 for the best solution to the August Reader Challenge, "Trimming Blanks." Shannon won second prize of $50. Here's a recap of the problem and the solution to the August Reader Challenge.
Problem:
Christine, a database analyst for a company that has several SQL Server installations, needs to trim leading and trailing blanks from the data in a table's text column. The following code creates the sample table containing the data she's working with:
CREATE TABLE #t (
id int NOT NULL IDENTITY PRIMARY KEY CLUSTERED,
t TEXT NULL
)
INSERT INTO #t VALUES( 'a ')
INSERT INTO #t VALUES ( 'b ')
INSERT INTO #t VALUES ( 'c d ')
INSERT INTO #t VALUES ( 'e')
INSERT INTO #t VALUES ( ' ')
INSERT INTO #t VALUES ( ' f')
INSERT INTO #t VALUES ( ' g ' + space(7000) )
INSERT INTO #t VALUES ( ' h j k l m ')
INSERT INTO #t VALUES ( 'This is a test')
Help Christine write the code to trim all leading and trailing blanks in the t column. The logic should accomplish the same result as applying LTRIM() and RTRIM() system functions on string data but can't use these functions.
Solution:
Christine solves her problem by using the PATINDEX() system function and the UPDATETEXT statement. The PATINDEX() function returns the first occurrence of a pattern's starting position and can be used to determine the position of the first or last character that isn't a space.
The following query returns the position of the first non-space character for each text value:
SELECT t, patindex( '%\[^ \]%', t ) AS "First non-space char position"
FROM #t
To return the position of the last non-space character followed by a space for each text value, Christine uses the following query:
SELECT t, patindex( '%\[^ \] ', t ) AS "Last non-space char position"
FROM #t
In the search pattern she specifies to PATINDEX(), the expression \[^ \] looks for any non-space character. Christine uses these two queries to derive the number of leading and trailing blanks that she needs to remove. She can use the SUBSTRING() system function to read a portion of the text data excluding blanks, but the length of the returned string can't exceed 8000 characters. This restriction prevents Christine from using SUBSTRING() directly with PATINDEX(). However, she can use the UPDATETEXT statement to remove the blanks from the text value by specifying the table and text column names, a text pointer to the data, and offset parameters.
The following code strips the leading and trailing blanks from the text data:
-- Data and length of data before trimming:
SELECT *, DATALENGTH(t) AS length FROM #t
GO
DECLARE @txtptr binary( 16 ), @pos int, @direction int, @ptrs CURSOR
SET @ptrs = CURSOR fast_forward FOR
SELECT txtptr, pos1, 1 AS direction
FROM (
SELECT TEXTPTR( t ), PATINDEX( '%\[^ \]%', t )-1, PATINDEX( '%\[^ \]%', t )
FROM #t
) AS t( txtptr, pos1, pos2 )
WHERE (pos1 > 0 or pos2 = 0) AND txtptr IS NOT NULL
UNION ALL
SELECT txtptr, pos1, 2
FROM (
SELECT TEXTPTR( t ), PATINDEX( '%\[^ \] ', t ), PATINDEX( '%\[^ \]%', t )
FROM #t
) AS t( txtptr, pos1, pos2 )
WHERE (pos1 > 0 or pos2 = 0) AND txtptr IS NOT NULL
OPEN @ptrs
WHILE( 1=1 )
BEGIN
FETCH @ptrs INTO @txtptr, @pos, @direction
IF @@fetch_status
The TEXTPTR() system function in the CURSOR query provides the pointer value that the UPDATETEXT statement uses to manipulate the text value. The direction column specified in the UNION ALL query indicates whether the code detected leading or trailing blanks in the data. The check for pos2 = 0 detects where the data consists of only spaces.
SEPTEMBER READER CHALLENGE:
Now, test your SQL Server savvy in the September Reader Challenge, "Restoring Database Backups" (below). Submit your solution in an email message to [email protected] by August 21. SQL Server MVP Umachandar Jayachandran, a SQL Server Magazine technical editor, will evaluate the responses. We'll announce the winner in an upcoming SQL Server Magazine UPDATE. The first-place winner will receive $100, and the second-place winner will receive $50.
Kumar is a DBA who manages several SQL Server 2000 installations. The databases are organized in filegroups and range in size from 50GB to 100GB. The following code creates a sample NorthwindDW database and tables that show the filegroups' layout:
CREATE DATABASE \[NorthwindDW\]
ON
PRIMARY( NAME = N'NorthwindDW_Data', FILENAME = N'C:\NorthwindDW_Data.mdf'),
FILEGROUP \[fg_NorthwindDW_Y2002\] (NAME = N'NorthwindDW_Y2002', FILENAME = N'C:\NorthwindDW_Y2002.ndf'),
FILEGROUP \[fg_NorthwindDW_Y2003\] (NAME = N'Northwind_Y2003', FILENAME = N'C:\NorthwindDW_Y2003.ndf')
LOG ON (NAME = N'NorthwindDW_Log', FILENAME = N'C:\NorthwindDW_Log.ldf')
COLLATE sql_latin1_general_cp1_ci_as
GO
USE NorthwindDW
GO
CREATE TABLE Products( ProductID int PRIMARY KEY )
CREATE TABLE Orders_M20020101(
OrderID int PRIMARY KEY, ProductID int REFERENCES Products ) ON fg_NorthwindDW_Y2002
CREATE TABLE Orders_M20020102(
OrderID int PRIMARY KEY, ProductID int REFERENCES Products ) ON fg_NorthwindDW_Y2002
CREATE TABLE Orders_M20030101(
OrderID int PRIMARY KEY, ProductID int REFERENCES Products ) ON fg_NorthwindDW_Y2003
CREATE TABLE Order_M20030102(
OrderID int PRIMARY KEY, ProductID int REFERENCES Products ) ON fg_NorthwindDW_Y2003
The sample database contains a filegroup for each year, with each filegroup containing fact tables only for its specific year. Kumar performs a full backup every Sunday and differential backups on the other days. The following list shows the three most recent backups:
- Sunday - Full Backup: NorthwindDW_full.bak
- Monday - Differential Backup: NorthwindDW_diff_1.bak
- Tuesday - Differential Backup: NorthwindDW_diff_2.bak
For testing purposes, Kumar needs to retrieve fact data for the year 2002 only and manipulate that data on a test SQL Server installation. Help Kumar restore the database backups efficiently, conserving disk resources and time.
