Find All Characters In A Column
Here's an updated way to find all characters in a column. Basically, I loop thru the ASCII readable characters and see if they are present in the column. I did skip A-Z and a-z. If you want it to include A-Z and a-z, just comment out the If statements at the bottom of the loop.
DECLARE @ColumnName varchar(200) = 'Text'
DECLARE @TableName varchar(200) = 'Rows'
DECLARE @SchemaName varchar(200) = 'SourceData'
DECLARE @Sql varchar(max)
DECLARE @MaxLength int = 126
DECLARE @Iterator int = 32
CREATE TABLE #AllChars
(
ColChar CHAR(1),
Instances int
)
WHILE @Iterator < @MaxLength
BEGIN
SET @Sql = 'INSERT INTO #AllChars (ColChar, Instances) SELECT CHAR(' + CAST(@Iterator as varchar) + '), COUNT(*) FROM ' + @SchemaName + '.' + @TableName + ' WHERE CHARINDEX(CHAR(' + CAST(@Iterator as varchar) + '), Text) > 0'
EXEC (@Sql)
SET @Iterator = @Iterator + 1
-- Skips A-Z
IF @Iterator = 65
SET @Iterator = 91
-- Skips a-z
IF @Iterator = 97
SET @Iterator = 123
END
SELECT *
FROM #AllChars
WHERE Instances > 0
ORDER BY colchar
DROP TABLE #AllChars
DECLARE @ColumnName varchar(200) = 'Text'
DECLARE @TableName varchar(200) = 'Rows'
DECLARE @SchemaName varchar(200) = 'SourceData'
DECLARE @Sql varchar(max)
DECLARE @MaxLength int = 126
DECLARE @Iterator int = 32
CREATE TABLE #AllChars
(
ColChar CHAR(1),
Instances int
)
WHILE @Iterator < @MaxLength
BEGIN
SET @Sql = 'INSERT INTO #AllChars (ColChar, Instances) SELECT CHAR(' + CAST(@Iterator as varchar) + '), COUNT(*) FROM ' + @SchemaName + '.' + @TableName + ' WHERE CHARINDEX(CHAR(' + CAST(@Iterator as varchar) + '), Text) > 0'
EXEC (@Sql)
SET @Iterator = @Iterator + 1
-- Skips A-Z
IF @Iterator = 65
SET @Iterator = 91
-- Skips a-z
IF @Iterator = 97
SET @Iterator = 123
END
SELECT *
FROM #AllChars
WHERE Instances > 0
ORDER BY colchar
DROP TABLE #AllChars
Comments