Tuesday, March 14, 2017

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

No comments: