How to colour columns of an SQL query in Latex Listings package?

Question

I am trying to find out how to define a style (or at least just a colour) on columns of an SQL query in the same way of the keywords style in the listings package. An example of the code can be found under this link https://github.com/davidstutz/latex-resources/blob/master/listings-sql/sql.tex

The current output of the listings package is:

I want to colour the columns ("firstname" in the example above) in the query automatically, just like any other SQL editors. Here is an example from DataGrip:

Answer 1

The package piton directly differentiates the names of the tables and the names of fields. However, piton requires LuaLaTeX.

\documentclass{report}
\usepackage{xcolor}
\usepackage{piton}
\begin{document}
\begin{Piton}[language=SQL]
select firstname from person
where firstnamew is null;
\end{Piton}
\end{document}

Answer 2

The following approach is based on pattern matching in the SQL query. If one of the predefined language keywords is encountered then a macro is called to examine this keyword. If the keyword is select or where then all following identifiers are printed in purple. If the keyword is from then the following identifiers will be black.

This is done by setting a boolean switch in the macro for the keyword style, and checking this switch in the macro for the identifier style. The keyword style macro also has \color{blue} at the end to set the color for the keywords.

Note that such a simple pattern matching approach may fail for more complex SQL queries. Also it is case sensitive, so SELECT firstname will not work.

MWE

\documentclass[a4paper,12pt]{article}

\usepackage[utf8]{inputenc}
\usepackage{amsmath}
\usepackage{amssymb}
\usepackage{xcolor}
\usepackage{listings}
\usepackage{xstring}

\definecolor{dkgreen}{rgb}{0,0.6,0}
\definecolor{ltgray}{rgb}{0.5,0.5,0.5}

\makeatletter
\newif\ifcolname
\colnamefalse

\def\keywordcheck{%
\IfStrEq*{\the\lst@token}{select}{\global\colnametrue}{}%
\IfStrEq*{\the\lst@token}{where}{\global\colnametrue}{}%
\IfStrEq*{\the\lst@token}{from}{\global\colnamefalse}{}%
\color{blue}%
}
\def\setidcolor{%
\ifcolname\color{purple}\else\color{black}\fi%
}
\makeatother

\lstset{%
    backgroundcolor=\color{white},
    basicstyle=\footnotesize,
    breakatwhitespace=false,
    breaklines=true,
    captionpos=b,
    commentstyle=\color{dkgreen},
    deletekeywords={...},
    escapeinside={\%*}{*)},
    extendedchars=true,
    frame=single,
    keepspaces=true,
    language=SQL,
    otherkeywords={is},
    morekeywords={*,modify,MODIFY,...},
    keywordstyle=\keywordcheck,
    identifierstyle=\setidcolor,
    numbers=left,
    numbersep=15pt,
    numberstyle=\tiny,
    rulecolor=\color{ltgray},
    showspaces=false,
    showstringspaces=false, 
    showtabs=false,
    stepnumber=1,
    tabsize=4,
    title=\lstname
}

\begin{document}

\begin{lstlisting}[language=sql]
select firstname, char_length(firstname), lastname from person
where firstnamew is null and lastname LIKE '%son';
\end{lstlisting}

\end{document}

Result: