Replace all special characters in string with T-SQL

Did a quick google search for “replace all special characters in string sql,” and didn’t see anything useful. Here’s my contribution.

And there are some interesting features that may not be in other similar scripts.

1. This code has the added functionality of logging every change into a table. That way, I can tell the people who sent the file what they need to clean up.

2. Also, more importantly, it uses dynamic SQL, and is not hard-coded to any table.  Pass in the table name, the column name of the ID column, and the name of the column that is checked for the special character.  Set the @Execute parameter to 0 to see the SQL that was generated to log and fix the special character.

3. The list of special characters are in a table.  The table also holds the new character that will replace the old one.

For any cursor objectors out there, I will explain why I used one.  Cursors have been much maligned in the SQL community, but it’s mostly because some people don’t understand set logic in any way, and only trust a row-by-row effort.  The cursor is necessary in this case, because I want to capture exactly what character was found.  It’s one character at a time,  not one row at a time.    A cursor is most elegant solution, in my opinion, as odd as this sounds.

First, set up the table and insert some special characters.  For this example, you need to create a schema called import, or just replace ‘import’ with ‘dbo’


CREATE TABLE [import].[SpecialCharacters](
	[SpecialCharactersID] [int] IDENTITY(1,1) NOT NULL,
	[OriginalChar] [char](1) NOT NULL,
	[NewChar] [varchar](1) NOT NULL,
	[SpecialCharacterName] [varchar](50) NULL,
	[CreatedDate] [datetime] NOT NULL
)
GO

ALTER TABLE [import].[SpecialCharacters]
ADD  CONSTRAINT [DF_SpecialC_CreatedDate]
DEFAULT (getdate()) FOR [CreatedDate]
GO

ALTER TABLE import.SpecialCharacters
ADD CONSTRAINT UC_OriginalChar
UNIQUE (OriginalChar)
GO

truncate table [import].[SpecialCharacters]

INSERT INTO [import].[SpecialCharacters]
( [OriginalChar] ,[NewChar] ,[SpecialCharacterName] )
VALUES('‘', '''', 'opening single quote')

INSERT INTO [import].[SpecialCharacters]
( [OriginalChar] ,[NewChar] ,[SpecialCharacterName] )
VALUES('’', '''', 'closing single quote')

INSERT INTO [import].[SpecialCharacters]
( [OriginalChar] ,[NewChar] ,[SpecialCharacterName] )
VALUES('“', '"', 'opening double quote')

INSERT INTO [import].[SpecialCharacters]
( [OriginalChar] ,[NewChar] ,[SpecialCharacterName] )
VALUES('”', '"', 'closing double quote')

INSERT INTO [import].[SpecialCharacters]
( [OriginalChar] ,[NewChar] ,[SpecialCharacterName] )
VALUES('`', '''', 'backtick')

INSERT INTO [import].[SpecialCharacters]
( [OriginalChar] ,[NewChar] ,[SpecialCharacterName] )
VALUES(CHAR(9), ' ', 'tab')

 

Next, build the stored procedure.


/*
Author: GlutenFreeSQL (Daniel Gras)
Original site:  glutenfreesql.wordpress.com
Date:   3/27/14
*/

CREATE PROCEDURE [import].[SpecialCharactersLog_SP]
(
@EntityType VARCHAR(256),
@EntityIDColumnName VARCHAR(256),
@ColumnName VARCHAR(256),
@Execute bit
)
AS
BEGIN

SET NOCOUNT ON;

DECLARE @SQLInsertStatement NVARCHAR(MAX);
DECLARE @SQLUpdateStatement NVARCHAR(MAX);

DECLARE @OldChar char(1),
@NewChar char(1),
@SpecialCharacterName varchar(50);

DECLARE SpecialCharacterCursor CURSOR FOR
SELECT [OriginalChar], [NewChar], [SpecialCharacterName]
FROM [import].[SpecialCharacters]

OPEN SpecialCharacterCursor

FETCH NEXT FROM SpecialCharacterCursor
INTO @OldChar, @NewChar, @SpecialCharacterName

WHILE @@FETCH_STATUS = 0
BEGIN

SET @SQLInsertStatement =
'INSERT INTO [import].[SpecialCharactersLog]
([CompanyName]
,[EntityType]
,[EntityID]
,[ColumnName]
,[OriginalValue]
,[NewValue]
,[SpecialCharacterName]
)
SELECT
CompanyName
, ''' + @EntityType + ''' AS EntityType
, ' + @EntityIDColumnName + ' AS EntityID
, ''' + @ColumnName + ''' AS ColumnName
, ' + @ColumnName + ' AS OriginalValue
, REPLACE( ' + @ColumnName + ', ''' + @OldChar + ''', '''
             + CASE @NewChar WHEN '''' THEN ''''''
               ELSE @NewChar
               END + ''') AS NewValue
, ''' + @SpecialCharacterName + ''' AS SpecialCharacterName
FROM dbo.' + @EntityType + 'Import
WHERE ' + @ColumnName + ' like ''%' + @OldChar + '%''
'

IF @Execute = 0
BEGIN
PRINT @SQLInsertStatement;
PRINT '';
END
ELSE
BEGIN
EXEC sp_executesql @SQLInsertStatement;
END

SET @SQLUpdateStatement =

'UPDATE dbo.' + @EntityType + 'Import
SET ' + @ColumnName + ' =
  REPLACE( ' + @ColumnName + ', ''' + @OldChar + ''',
  ''' + CASE @NewChar WHEN '''' THEN '''''' ELSE @NewChar END + ''')
WHERE ' + @ColumnName + ' LIKE ''%' + @OldChar + '%''
'

IF @Execute = 0
BEGIN
PRINT @SQLUpdateStatement;
PRINT '';
END
ELSE
BEGIN
EXEC sp_executesql @SQLUpdateStatement;
END

FETCH NEXT FROM SpecialCharacterCursor
INTO @OldChar, @NewChar, @SpecialCharacterName

END

CLOSE SpecialCharacterCursor ;
DEALLOCATE SpecialCharacterCursor ;

END

Then, execute the stored procedure.

exec [import].[SpecialCharactersLog_SP] 
'TableName', 
'IDColumnName', 
'ValueColumnName', 1

Leave a comment

Filed under Uncategorized

SSIS ErrorCode and ErrorColumn

SSIS is a total jerk.  It provides an ErrorCode and ErrorColumn on an error path, but then gives you no way to automatically translate the meaning of those codes.  For example, I’m getting ErrorCode -2146233033 and ErrorColumn 743.

When I use the code for the script component suggested by Microsoft, (this.ComponentMetaData.GetErrorDescription(Row.ErrorCode)), then it gives me the error “CATASTROPHIC ERROR“.    So we struck out on that solution.

There’s a few incredibly complicated solutions out there, and some third party plug-ins.  but I’m not sure I want to go down that road.

I’ll be better off just to dump the results of the error into a table, then do my own validation, and manually construct the error messages.

The other annoying thing is that in the XML Source component, it resets itself every time you look at the columns or error output tabs.

Leave a comment

Filed under Uncategorized

Crowd deadlock

There is a 3rd party tool at my company that is causing a lot of deadlocks after an upgrade.

The main problem for me is that it fills the SQL error logs with error messages.   This was happening 20 times a day.

It happens when two of these SQL update statements run simultaneously, and one of them gets killed.

update cwd_user_attribute
set user_id=@P0, directory_id=@P1, attribute_name=@P2, attribute_value=@P3, attribute_lower_value=@P4
where id=@P5

Here is the problem.  Since this statement updates the user_id (maybe unnecessarily), SQL has to make sure the foreign key constraint is still valid, by checking the table dbo.cwd_user.   But this causes deadlocks in some situations.   Although I rarely recommend this, removing this foreign key would remove the deadlocks.

I’m guessing since it’s a third party tool, the SQL can’t be changed.  But if it can, I would recommend not updating user_id, since that is the logical key of the table, and is not changing anyway.  I see this kind of thing a lot with the ORMs.

Here is the drop foreign key command:

ALTER TABLE dbo.cwd_user_attribute
DROP CONSTRAINT fk_user_attribute_id_user_id

Here is the deadlock graph:

Image

Leave a comment

March 11, 2014 · 3:15 pm

SQL Join syntax delayed ON operator

I learned something new today. In JOIN T-SQL syntax, the ON is not necessary immediately.   I’m not sure what benefit this would give, but is a nice piece of trivia.

For example, the following query WORKS in the AdventureWorks2012 database:

SELECT *
FROM HumanResources.EmployeePayHistory
INNER JOIN HumanResources.Employee
INNER JOIN HumanResources.EmployeeDepartmentHistory
INNER JOIN HumanResources.Department

ON Department.DepartmentID =
   EmployeeDepartmentHistory.DepartmentID

ON EmployeeDepartmentHistory.BusinessEntityID =
   Employee.BusinessEntityID

ON EmployeePayHistory.BusinessEntityID =
   Employee.BusinessEntityID

The order of the ONs matters.   The last two tables joined need to have the first ON, continuing in reverse order.

Leave a comment

Filed under Uncategorized

ssrs report stuck on loading

For me, it just had trouble rendering a simple line that was drawn inside the header.

Leave a comment

Filed under Uncategorized

SSAS reserved word error

Errors in the metadata manager. 

The ‘Customers’ dimension contains a member property with invalid name: ‘Name’ is one 
of the reserved words.

Despite renaming everything in sight that had the word Name, it was still giving me this error.

I just had to delete the Customer dimension, then build it again.

Leave a comment

Filed under Uncategorized

Profiler on Prod

My whole career I’ve heard that running SQL Server Profiler is a low cost operation, and has little effect on the server.  And I believed this within reason, of course, because I always added a lot of filters.

But recently when trying to get understand a process that runs in the middle of the night, I came across a problem.  In the morning, my Profiler trace was filled with the message “Trace Skipped Records”.

This eventually led me to a useful blog post by Grant Fritchey:  Snags With Profiler GUI, which led to another post, Profiler Research, which outlines why a client-side trace is inefficient.

Finally, since now I need to re-learn how to do server-side traces, I am following a Stairway Series on SQL Server Central.  Stairway to Server-Side Tracing

So far it seems a lot easier, because I can query the trace directly using SQL, to further filter out results. And I bet I can schedule it to kick off in the middle of the night.

Leave a comment

Filed under Uncategorized