How to add syntax colored and well formatted SQL in your wordpress blog

People always post SQL code into blogs and forums need a tool to help them to prepare dull monochrome, badly-formatted SQL code for HTML-based publication in a nice clean style.

SQL Pretty Printer is able to output HTML in both <code> and <pre> blocks in a convenient way with code like this:

	<div><span style="font-family: Courier New; font-size: 10pt;">
	<span style="color: blue; ">WITH</span> <span style="color: maroon; ">mycte</span><span style="color: maroon; ">(</span><span style="color: maroon; ">x</span><span style="color: maroon; ">)</span>
	<br/>     <span style="color: blue; ">AS</span> <span style="color: maroon; ">(</span><span style="color: blue; ">SELECT</span> <span style="color: maroon; ">x</span> <span style="color: silver; ">=</span> <span style="color: fuchsia; font-style: italic; ">CONVERT</span><span style="color: maroon; ">(</span><span style="color: black; font-style: italic; ">VARCHAR</span><span style="color: maroon; ">(</span><span style="color: black; ">1000</span><span style="color: maroon; ">)</span><span style="color: silver; ">,</span> <span style="color: red; ">'hello'</span><span style="color: maroon; ">)</span>
	<br/>         <span style="color: blue; ">UNION</span> <span style="color: blue; ">ALL</span>
	<br/>         <span style="color: blue; ">SELECT</span> <span style="color: fuchsia; font-style: italic; ">CONVERT</span><span style="color: maroon; ">(</span><span style="color: black; font-style: italic; ">VARCHAR</span><span style="color: maroon; ">(</span><span style="color: black; ">1000</span><span style="color: maroon; ">)</span><span style="color: silver; ">,</span> <span style="color: maroon; ">x</span> <span style="color: silver; ">+</span> <span style="color: red; ">'a'</span><span style="color: maroon; ">)</span>
	<br/>         <span style="color: blue; ">FROM</span>   <span style="color: maroon; ">mycte</span>
	<br/>         <span style="color: blue; ">WHERE</span>  <span style="color: fuchsia; font-style: italic; ">Len</span><span style="color: maroon; ">(</span><span style="color: maroon; ">x</span><span style="color: maroon; ">)</span> <span style="color: silver; "><</span> <span style="color: black; ">10</span>
	<br/>         <span style="color: blue; ">UNION</span> <span style="color: blue; ">ALL</span>
	<br/>         <span style="color: blue; ">SELECT</span> <span style="color: fuchsia; font-style: italic; ">CONVERT</span><span style="color: maroon; ">(</span><span style="color: black; font-style: italic; ">VARCHAR</span><span style="color: maroon; ">(</span><span style="color: black; ">1000</span><span style="color: maroon; ">)</span><span style="color: silver; ">,</span> <span style="color: maroon; ">x</span> <span style="color: silver; ">+</span> <span style="color: red; ">'b'</span><span style="color: maroon; ">)</span>
	<br/>         <span style="color: blue; ">FROM</span>   <span style="color: maroon; ">mycte</span>
	<br/>         <span style="color: blue; ">WHERE</span>  <span style="color: fuchsia; font-style: italic; ">Len</span><span style="color: maroon; ">(</span><span style="color: maroon; ">x</span><span style="color: maroon; ">)</span> <span style="color: silver; "><</span> <span style="color: black; ">10</span><span style="color: maroon; ">)</span>
	<br/><span style="color: blue; ">SELECT</span> <span style="color: maroon; ">x</span>
	<br/><span style="color: blue; ">FROM</span>   <span style="color: maroon; ">mycte</span>
	<br/><span style="color: blue; ">ORDER</span>  <span style="color: blue; ">BY</span> <span style="color: fuchsia; font-style: italic; ">Len</span><span style="color: maroon; ">(</span><span style="color: maroon; ">x</span><span style="color: maroon; ">)</span><span style="color: silver; ">,</span>
	<br/>          <span style="color: maroon; ">x</span><span style="color: silver; ">;</span> 
	</span></div>

You can copy and embedded above HTML code into your blog to get a colored SQL like this:


WITH mycte(x)

     AS (SELECT x = CONVERT(VARCHAR(1000), ‘hello’)

         UNION ALL

         SELECT CONVERT(VARCHAR(1000), x + ‘a’)

         FROM   mycte

         WHERE  Len(x) < 10

         UNION ALL

         SELECT CONVERT(VARCHAR(1000), x + ‘b’)

         FROM   mycte

         WHERE  Len(x) < 10)

SELECT x

FROM   mycte

ORDER  BY Len(x),

          x; 

But in wordpress, the output will be looked like this that add additional lines.


WITH mycte(x)

     AS (SELECT x = CONVERT(VARCHAR(1000), ‘hello’)

         UNION ALL

         SELECT CONVERT(VARCHAR(1000), x + ‘a’)

         FROM   mycte

         WHERE  Len(x) < 10

         UNION ALL

         SELECT CONVERT(VARCHAR(1000), x + ‘b’)

         FROM   mycte

         WHERE  Len(x) < 10)

SELECT x

FROM   mycte

ORDER  BY Len(x),

          x; 

It seems that editor of wordpress add an additional <br/> tag at the end of each line. this issue can be resolved by changing

function wpautop($pee, $br = 1) {

to

function wpautop($pee, $br = 0) {

in wp-includes/formatting.php

Newsletter Updates

Enter your email address below to subscribe to our newsletter