|
|
Search and Replace in a Text Column
SQL Server has a replace function, but it doesn't work on "text" columns. SQL Server seems to address this with a new varchar(max) datatype, but those of us still using SQL Server 2000 still need a solution. The following stored procedure should do the trick. The seed of the idea for this came from the following page:
http://www.sqlteam.com/article/search-and-replace-in-a-text-column
My version puts the code in a stored procedure, replaces all instances of the string, escapes any wildcard characters that might be in the string, and also prints out the number of replacements that are done. It is limited in that it won't work if the new text contains the old text, because that would cause an infinite loop, but maybe we'll be using varchar(max) before we need to do that!
- David ( dave@modernsignal.com)
Usage:
EXEC usp_utility_replacetext 'Content','htmlContent','oldtext','newtext'
CREATE PROCEDURE dbo.usp_utility_ReplaceText
@TableName VARCHAR(32),
@ColumnName VARCHAR(32),
@otxt VARCHAR(100),
@ntxt VARCHAR(100)
AS
DECLARE @num INT
DECLARE @sql NVARCHAR(4000)
DECLARE @parms NVARCHAR(4000)
DECLARE @invalidChars VARCHAR(20)
SET @invalidChars = '%[^a-z1-9_@#$]%'
IF PATINDEX(@invalidChars, @TableName) > 0
BEGIN
PRINT 'Invalid character in table name.'
RETURN
END
IF PATINDEX(@invalidChars, @ColumnName) > 0
BEGIN
PRINT 'Invalid character in column name.'
RETURN
END
IF CHARINDEX(@ntxt, @otxt) > 0
BEGIN
PRINT 'Sorry, the new text cannot contain the old text.'
RETURN
END
SET @sql = N'
DECLARE @txtlen INT
DECLARE @ptr BINARY(16)
DECLARE @pos INT
DECLARE @otxtPat VARCHAR(100)
DECLARE @more bit
SET @txtlen = LEN(@otxtIN)
SET @otxtPat = @otxtIN
SET @otxtPat = REPLACE(@otxtPat,''%'',''[%]'')
SET @otxtPat = REPLACE(@otxtPat,''_'',''[_]'')
SET @otxtPat = ''%'' + @otxtPat + ''%''
SET @numOUT = 0
WHILE EXISTS (
SELECT * FROM ' + @TableName + '
WHERE ' + @ColumnName + ' LIKE @otxtPat
)
BEGIN
DECLARE curs CURSOR local fast_forward
FOR SELECT
TEXTPTR(' + @ColumnName + '),
PATINDEX(@otxtPat,
' + @ColumnName + ') - 1
FROM ' + @TableName + '
WHERE ' + @ColumnName + ' LIKE @otxtPat
OPEN curs
FETCH NEXT FROM curs INTO @ptr, @pos
WHILE @@fetch_status = 0
BEGIN
UPDATETEXT
' + @TableName + '.' + @ColumnName + '
@ptr @pos @txtlen @ntxtIN
SET @numOUT = @numOUT + 1
FETCH NEXT FROM curs INTO @ptr, @pos
END
CLOSE curs
DEALLOCATE curs
END'
SET @parms = N'@otxtIN VARCHAR(100),@ntxtIN VARCHAR(100),@numOUT INT OUTPUT'
EXEC sp_executesql @sql, @parms,
@otxtIN = @otxt,
@ntxtIN = @ntxt,
@numOUT = @num OUTPUT
PRINT CONVERT(VARCHAR(32), @num) + ' occurrences replaced.'
GO
|
October 1, 2009 -- Modern Signal launches the new website for Charm City Run.The site features a complete redesign,
full content management system, enhanced admin tools to manage events and programs, and an events calendar. -View-
August 3, 2009 -- Modern Signal launches a redesigned website to showcase the 2010 International Builders’ Show. Along with other functionality additions, we’ve integrated Google search appliance to produce faster and more precise search results. –View the site-
June 29, 2009 -- Modern Signal launches several features for The NALP Foundation for Law Career Research and Education website: a front-end bookstore with shopping cart functionality, back-end products and orders administration tools, and a front-end donation form. A full e-commerce system was also implemented to support the front-end features. -View-
May 13, 2009 -- In conjunction with Levine & Associates, Modern Signal launches newly structured Lamb, Beef, and Trade web sites
for Meat and Livestock Australia, Ltd. (MLA). The 3 sites, including Spanish
versions, are powered through a shared CMS to streamline and consolidate
content creation. With this launch, an admin tool was also implemented to
manage recipes across all sites.
-- View portal page to all sites --
January 26, 2009 -- 
| A newly redesigned website is launched for Woodbourne, a private nonprofit child welfare organization. The launch included a new design, full CMS, video integration on the homepage (video by Houpla!), contact us form, and an I-frame admin tool for staff to manage Google maps displayed in the site. -View-
|
January 9, 2009 -- 
| | Modern Signal launches the new website for NALP, The Association for Legal Career Professionals. The site features a complete redesign, full content management system, members-only content, online bookstore, blogs, and advertising management tools for NALP staff. -View-
|
December 12, 2008 --
 |
| Phase II of Dance/USA website redesign and development project has launched. The new launch features a full Content Management system, homepage flash design, news/promotions tool, as well as a full events registration module. -View- |
November 2008 --
 |
|
In conjunction with Burka Studios, Modern Signal designed and developed the JStreet Companies website. The website launch included a new design, full CMS integration, residential listings tool and MRIS integration. Through the site, users can search for residential properties that are either listed by the Randall Hagner company or are listed by MRIS. Users may also save their favorite listings, search for agents and submit their own listings for sale by Randall Hagner. -View-
|
|
 |