Js parse sql and extract table name alias

April 22, 2022

I have a sql query and i want to extract table alias。
for example:

select * from t_1 a join t_2 b on a.id = b.t_id where a.words = ? and b.words = ?

the result I expected

a: t_1, b: t_2

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.
Visit Medevel

>Solution :

You could try a regex match approach to capture every table name and alias.

var sql = "select * from t_1 join t_2 b on t_1.id = b.t_id where t_1.words = ? and b.words = ?";
var aliases = sql.match(/(?<=\b(?:from|join) )\w+(?: (?!\bjoin\b)\w+)?/g);
aliases.forEach(x => console.log((x.split(" ")[1] ? x.split(" ")[1] : x.split(" ")[0]) + ": " + x.split(" ")[0]));

Note that in general to do what you want would require a formal SQL parser. Regex alone is not suitable, and it would take hundreds of lines of code, most likely more.