CHAR(13) Prints an Invisible Character

Question

Post reply

CHAR(13) Prints an Invisible Character

John Keadle

SSC Enthusiast

Points: 177
More actions
April 25, 2006 at 8:32 am

#90892

I have this sweet query that cursors through Information_Schema.Columns and creates TSQL Code that creates a view for every table using an extended property called "caption" as the column header.
The code that actually creates the print statements that create the CREATE VIEW queries looks kind of like this:
BEGIN -- New Table because it's the first table; no footer required for old table.
SET @strFinalString =
'--Create View vw' + @strTableName + + CHAR(13) +
'CREATE VIEW [dbo].[vw' + @strTableName + '] AS ' + CHAR(13) +
'SELECT ' + CHAR(13) +
' [' + @strColumnName + '] AS [' +
@strColumnCaption + ']'
<loop through columns here>
END
SET @strFinalString = @strFinalStirng + 'From ' + @strCurrentTable + CHAR(13) + 'GO' + CHAR(13)
PRINT @strFinalString
This code is not enitely correct but maybe you get the idea. The outpur looks like this all nice and pretty:
--Create View vwOutputCREATE VIEW [dbo].[vwOutput]
AS SELECT
[OutputID] AS [OutputID],
[Message] AS [Message]
From [Output]
GO
The problem I'm having is the CHAR(13). That formats the output nicely but it uses an invisible character to do it. When you copy and paste the output into a query window, the invisible character blows up the query. If you copy and paste it into Notepad, you can see the little rectangles where the CHAR(13)s go.
Is there something I can use to get the new line that won't write this invisible character?
I'd really like to get this tool to work.
Thanks!
JK

-robot

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

paul_richardson SSCarpal Tunnel Points: 4594 More actions · Answer 1

hi,

try using convert(char(1),0x0a) instead of Char(13).

select 'A' + convert(char(1),0x0a) + 'B'

returns :-

----

A

B

hope this helps

Paul

John Keadle SSC Enthusiast Points: 177 More actions · Answer 2

That works nicely, thanks!

What's it do?

I see CHAR(1) is "Start of Heading" but I have no idea what happens when do a convert and pass it an 0x0a.

What's an 0x0a?

Thanks again!

JK

-robot

paul_richardson SSCarpal Tunnel Points: 4594 More actions · Answer 3

JK,

It's something i found in a system stored procedure years ago, and it was used to format output. I'm not sure what it does exactly, i just know that it's useful in these type of situations

Paul

Eddie Wuerch SSChampion Points: 12535 More actions · Answer 4

0x0a (hexadecimal) = decimal 10

char(10) = line feed

select 'A' + convert(char(1),0x0a) + 'B'

is the same as

select 'A' + char(10) + 'B'

John: the char(1) in the convert statment refers to the char datatype, length of 1. The char(10) in the string concat refers to the char() function.

Don't they teach hex anymore?

-Eddie

Eddie Wuerch
MCM: SQL

John Keadle SSC Enthusiast Points: 177 More actions · Answer 5

Hex? Maybe in computer science.

I have humanities degree and depend on the kindness of people like you to help me through the tough parts.

If you ever need to know what day they started digging the Suez canal, drop me a note.

Thanks for the note and thanks to Paul for unearthing this gem.

JK

-robot

Protiguous SSC Eights! Points: 915 More actions · Answer 6

I believe the char(1) is what to convert to, not a character.

0x0a is a Linefeed (could also use char # 10).

0x0d is a carriage return (same as your char #13).

Next time you create a text file in notepad, view it with a hex-editor.

You'll see 0d 0a at the end of every line where you pressed "Enter".

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 7

You'll have better luck in general with linefeed (CHAR(10)) than carriage return (CHAR(13)).

As pointed out already, CONVERT(CHAR(1), 0x0A) is just a long-winded way of saying CHAR(10).

Scott Beckstead Right there with Babe Points: 780 More actions · Answer 8

I can't believe that anybody would NOT know what char(10) and char(13) are. What are they teaching in school these days. I'm flabberghasted!

Scott

Kindest Regards,

Scott Beckstead

"We cannot defend freedom abroad by abandoning it here at home!"
Edward R. Murrow

scottbeckstead.com

John Keadle SSC Enthusiast Points: 177 More actions · Answer 9

I can't believe you think some of us actually care what you believe.

If you can't follow the thread, move on. Nobody's talking about what Char(13) and Char(10) mean only why one blows up your query and the other doesn't.

As far as the current curricula in "schools these days", we've already covered that, except to point out that, in computer science classes, they are obvoiusly teaching neither kindness nor any aversion to trite and cliche'. If you know everything there is to know about SQL then please excuse any of us that might be more forgetful than yourself; we lurk about message boards like this one exactly because we don't know everything you know struggling to find answers to questions via paths of least resistance. I apologize if this one ventured too near your consciousness.

And, as a footnote, the word is spelled F-L-A-B-B-E-R-G-A-S-T-E-D

It means struck dumb. That makes you half right; I am not persuaded you've been struck.

JK

-robot

Protiguous SSC Eights! Points: 915 More actions · Answer 10

Whoa.. we're all tryin' to help you here. Prozac anyone?

All Scott was saying is that the most basic stuff seems to have not been taught in school.

Anyways.

My point is that a normal "dos" type text file contains newlines and carriage returns. When notepad only sees one (?13?) it doesn't quite know how to interpret it.

Your question was: "Is there something I can use to get the new line that won't write this invisible character?"

The answer to that question is to know what you are writing (only a #13) and why it displays that way (because notepad doesn't know how to handle it).

If you are taking a computer class and the teacher has not shown this information, then maybe "us" exclaiming our surprise may stimulate you to prompt your teachers for more information.

No one knows everything there is to know about SQL. Those who think they do can't learn anymore.

I've been lurking here for the past year... this forum is great for finding solutions.

John Keadle SSC Enthusiast Points: 177 More actions · Answer 11

My point is I didn't learn any of this in school.

I learned it from places like this.

I just wanted my query to run and, now, thanks to the explanation, it does. Anything beyond that is gold plating.

Thanks for helping!

JK

-robot

Gabor Nyul SSCrazy Eights Points: 9459 More actions · Answer 12

Nowadays in the schools they are not teaching the ascii representation of the caracters.

The bigger issue is with the non english speaking countries, like mine in Hungary where we have special caracters not including in the English code page. Therefore there is a difference of interpretation of the DOS and Windows (OEM, ANSI) code page for the same caracter. What a mess!

Newer the less you have to know that text editors are interpreting differently the CF LF characters. What is not working in notepad is looking nice in wordpad.

And also the order has its importance.

You have to enter select 'ABC' + char(13) + char(10) + 'ABC' (and not char(10) + char(13)) becase the text editors are interpreting the right order.

So as a final advice put in your query and addition char(10) to the char(13) and it will work fine

Bye
Gabor