Menu
When trying to identify erroneous data (often needing manual review and removal), I'd like an easy way of seeing hidden characters, such as TAB, Space, Carriage return and Line feed. Is there a built-in way for this?
In a similar question here on stackoverflow, regarding Oracle, a DUMP(fieldname) function was suggested, but I don't know if that woud make things easier even if a corresponding function would exist in SQL Server, since I need to see the Characters in their context.
The best idea I could come up with was replacing the expected hidden characters with visible ones, like this:
SELECT REPLACE(REPLACE(REPLACE(REPLACE(myfield, ' ', '˙'), CHAR(13), '[CR]'), CHAR(10), '[LF]'), CHAR(9), '[TAB]') FROM mytable
Is there a better way? I don't like this way since there might be other less common hidden characters that are not taken into account by me such as vertical TAB etc... Turning on "show hidden characters", as you can do in almost any text editor, would be such a nice feature in SQL Server Query Analyzer, so I almost expect that it can be done somehow in SQL server as well... or at least that someone has an even better idea than mine, to show this kind of white space info.
I just noticed that there is a built-in way to see "white space", not in SQL Query Analyzer, but in the part of the interface that once was the SQL Enterprise manager. Right-click a table in SQL Management Studio Object Explorer tree, and select "Edit top 200 rows". In the result white space (at least CR LF) is visible as empty squares.
670
In this article, we covered the important SQL string functions TRIM and LENGTH to learn how to remove junk characters in SQL. If you want to detect hidden or unwanted characters as part of an initial diagnosis, use LENGTH . Then, use TRIM to get rid of unwanted characters.
Declare @nl Char(2) = char(13) + char(10) Declare @tab Char(1) = char(9) etc... Then you can use those declared variables anywhere in the rest of the proc without loss of clarity...
A wildcard character is used to substitute one or more characters in a string. Wildcard characters are used with the LIKE operator. The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.
LEN() function calculates the number of characters of an input string, excluding the trailing spaces. It is an expression that can be a constant, variable, or column of either character or binary data. Returns : It returns the number of characters of an input string, excluding the trailing spaces.
Create a function that addresses all the whitespace possibilites and enable only those that seem appropriate:
SELECT dbo.ShowWhiteSpace(myfield) from mytableUncomment only those whitespace cases you want to test for:
CREATE FUNCTION dbo.ShowWhiteSpace (@str varchar(8000)) RETURNS varchar(8000) AS BEGIN DECLARE @ShowWhiteSpace varchar(8000); SET @ShowWhiteSpace = @str SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(32), '[?]') SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(13), '[CR]') SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(10), '[LF]') SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(9), '[TAB]') -- SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(1), '[SOH]') -- SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(2), '[STX]') -- SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(3), '[ETX]') -- SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(4), '[EOT]') -- SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(5), '[ENQ]') -- SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(6), '[ACK]') -- SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(7), '[BEL]') -- SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(8), '[BS]') -- SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(11), '[VT]') -- SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(12), '[FF]') -- SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(14), '[SO]') -- SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(15), '[SI]') -- SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(16), '[DLE]') -- SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(17), '[DC1]') -- SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(18), '[DC2]') -- SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(19), '[DC3]') -- SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(20), '[DC4]') -- SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(21), '[NAK]') -- SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(22), '[SYN]') -- SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(23), '[ETB]') -- SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(24), '[CAN]') -- SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(25), '[EM]') -- SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(26), '[SUB]') -- SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(27), '[ESC]') -- SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(28), '[FS]') -- SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(29), '[GS]') -- SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(30), '[RS]') -- SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(31), '[US]') RETURN(@ShowWhiteSpace) ENDThey way I did it was by selecting all of the data
select * from myTable and then right-clicking on the result set and chose "Save results as..." a csv file.
Opening the csv file in Notepad++ I saw the LF characters not visible in SQL Server result set.
36
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
