Jak v databázi zaměnit v textu jednu frázi za jinou

26. 8. 2009 20:45:38

Tak jsem zkusil dát tohle do SQL query, a nefunguje to:

(zdroj: http://vyaskn.tripod.com/sql_server_search_and_replace.htm)

CREATE PROC SearchAndReplace(	@SearchStr nvarchar(100),	@ReplaceStr nvarchar(100))ASBEGIN	-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.	-- Purpose: To search all columns of all tables for a given search string and replace it with another string	-- Written by: Narayana Vyas Kondreddi	-- Site: http://vyaskn.tripod.com	-- Tested on: SQL Server 7.0 and SQL Server 2000	-- Date modified: 2nd November 2002 13:50 GMT	SET NOCOUNT ON	DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110), @SQL nvarchar(4000), @RCTR int	SET  @TableName = ''	SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')	SET @RCTR = 0	WHILE @TableName IS NOT NULL	BEGIN		SET @ColumnName = ''		SET @TableName = 		(			SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))			FROM 	INFORMATION_SCHEMA.TABLES			WHERE 		TABLE_TYPE = 'BASE TABLE'				AND	QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName				AND	OBJECTPROPERTY(						OBJECT_ID(							QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)							 ), 'IsMSShipped'						       ) = 0		)		WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)		BEGIN			SET @ColumnName =			(				SELECT MIN(QUOTENAME(COLUMN_NAME))				FROM 	INFORMATION_SCHEMA.COLUMNS				WHERE 		TABLE_SCHEMA	= PARSENAME(@TableName, 2)					AND	TABLE_NAME	= PARSENAME(@TableName, 1)					AND	DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')					AND	QUOTENAME(COLUMN_NAME) > @ColumnName			)				IF @ColumnName IS NOT NULL			BEGIN				SET @SQL=	'UPDATE ' + @TableName + 						' SET ' + @ColumnName 						+ ' =  REPLACE(' + @ColumnName + ', ' 						+ QUOTENAME(@SearchStr, '''') + ', ' + QUOTENAME(@ReplaceStr, '''') + 						') WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2				EXEC (@SQL)				SET @RCTR = @RCTR + @@ROWCOUNT			END		END		END	SELECT 'Replaced ' + CAST(@RCTR AS varchar) + ' occurence(s)' AS 'Outcome'END

---------- Post was amended at 21:10 ----------

Nakonec jsem svůj problém vyřešil pluginem do wordpressu :thumbup:

26. 8. 2009 20:45:38