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