Find the code behind

— If you need to split strings on specific characters
— and experiencing problem finding the right character
— or the UNICODE code of the character this might come in handy.
—
— The reason behind this post is that I needed to identify the character code

— behind a junk character.

—
— It utilizes a cursor because I need to loop through the string or strings.
— The cursor fetch every single string, and the loop reads the string.
—
— Valiables for the string
DECLARE @string nvarchar(255) = N”, @stringnumber int = 0, @length int = 0

— Declaring the cursor
DECLARE StringCursor CURSOR FOR
— Within the block comment you’ll find a sample query.
— * {rowidentifier} = The column that identifies the row in table
— * {stringcolumn} = The column that needs to be evaluated
— * {sourcetable} = The table that contains your strings
— * {filtercolumn} = The column that identifies your row containing the string
— * {filtervalue} = The value that filter
/*
SELECT {rowidentifier}, {stringcolumn} FROM {sourcetable} WHERE {filtercolumn} = {filtervalue}
*/
— Just to get some sample data.
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
name
FROM sys.databases (NOLOCK)
— Please note that ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) is
— just something that gives you the read order position of the string
— and should probably be a reference to the row identifier.

— A variable containg the target table
DECLARE @StringTable TABLE (
String nvarchar(255) NOT NULL,
— String to be evaluated
StringNumber int NOT NULL,
— Number representing the physical order in table
Position int NOT NULL,
— Position in string for the Character
UNICODECharacter nchar(1) NOT NULL,
— Character to be decoded
UNICODECode AS UNICODE(UNICODECharacter)
— The UNICODE for specific character.
)

— In this example I use a cursor. Remember that cursors might be bad
— and if the source table contain long words and long strings this might take
— a while. Due to this I choose to add the NOLOCK hint on the query for the cursor.
OPEN StringCursor
FETCH NEXT FROM StringCursor INTO @stringnumber, @string

WHILE @@FETCH_STATUS = 0
— As long the FETCH NEXT grabs a row this is true.
BEGIN

— Variables for the position of the character and character itself.
DECLARE @pos int = 1, @char nchar(1) = N”
SET @length = LEN(@string)
— Length of the evaluated string.
WHILE @pos — Will continue to loop while we have a character to read.
BEGIN
— Inserts the table valued variable
INSERT INTO @StringTable (String, StringNumber, Position, UNICODECharacter)
VALUES(@string, @stringnumber, @pos, SUBSTRING(@string, @pos, 1))
SET @pos += 1
— Moves to next position in the string.
END
— Grab the next string from the table.
FETCH NEXT FROM StringCursor INTO @stringnumber, @string
END

— The CLOSE and DEALLOCATE is important and usually forgotten and if left out
— probably leads to excessive, unnecessary locking.
CLOSE StringCursor
DEALLOCATE StringCursor

— See the result.
SELECT * from @StringTable

— Have fun, I hope you enjoyed the script.
— /Mattias