SQL: ASCII characters code for each character of a field (all records)

I wanted to look for all non-standard (i.e. A-Z/a-z/0-9) characters in a field across all records in a table. Therefore, I have adjusted my previous script to do so:

SET NOCOUNT ON;
CREATE TABLE #Characters
( ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
  RecordID INT NOT NULL,
  [Character] NCHAR(1),
  [ASCIIValue] INT
)
DECLARE @RecordID INT;
DECLARE @counter int = 1;
DECLARE @colString NVARCHAR(MAX);
DECLARE search CURSOR FAST_FORWARD FOR
	SELECT RecordID, TextString
	FROM tableName WHERE ISNULL(TextString, '') <> ''

OPEN search

FETCH NEXT FROM search INTO @RecordID, @colString

WHILE @@FETCH_STATUS = 0
BEGIN
	SET @counter = 1
	WHILE @counter <= DATALENGTH(@colString)
	BEGIN
		INSERT INTO #Characters (RecordID, [Character], [ASCIIValue])
		SELECT @RecordID,
			CHAR(ASCII(SUBSTRING(@colString, @counter, 1))) as [Character],
			ASCII(SUBSTRING(@colString, @counter, 1)) as [ASCIIValue]
		SET @counter = @counter + 1
	END

	FETCH NEXT FROM search INTO @RecordID, @colString
END
CLOSE search
DEALLOCATE search

SELECT * FROM #Characters
WHERE (ASCIIValue < 48 OR (ASCIIValue > 57 -- 0-9
  AND ASCIIValue < 65) OR (ASCIIValue > 90 -- A-Z
  AND ASCIIValue < 97) OR ASCIIValue > 122) -- a-z
ORDER BY ASCIIValue

DROP TABLE #Characters
SET NOCOUNT OFF
GO

Simply change to the required database, and change the select statement in the cursor.

Please always test on a development server/database before running on Production.

Updated: Tuesday, 29th January, 2019 — 3:59 pm

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.