Skip to content
No results
We use cookies to ensure that we give you the best experience on our website.

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

- admin
- October 4, 2011
- formatting, sql, wordpress

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

I accept the Privacy Policy

### Related Posts

#### Gudu SQLFlow direct data flow introduction

- May 26, 2022

#### Introduction to where-group-by clause in Gudu SQLFlow indirect data flow

- May 25, 2022

#### How to Get Data Lineage by Analyzing Oracle’s SQL Statements and Stored Procedures?

- May 23, 2022