Regexp convert Sql Server TOP syntax with Postgresql LIMIT syntax

Question

I have an sql server query like:

Select TOP 2 * from customer order by id desc

and want to replace it with;

Select * from customer order by id desc LIMIT 2

How do I do that using regex? The query might have subqueries that also include TOP statements. Basically, I want to replace TOP statement with LIMIT, to have the query work with Postgresql

I removed the SQL tag. It is only incidental that your string represents a SQL query. You are looking for help in C# using regular expressions. — Gordon Linoff, Oct 24 '20 at 12:48
Please give the most complex example you will want to deal with also — Caius Jard, Oct 24 '20 at 12:49
Sorry, let's forget about the subqueries and more complex statements. This simple query should do it. Thanks. — Osi, Oct 24 '20 at 12:55

Caius Jard · Accepted Answer · 2020-10-24T16:16:19.353

-1

Simplistically in .NET syntax this could look like a (case insensistive) regex of:

SELECT TOP (?<t>\d+)(?<q>.*?ORDER BY[ a-z]+)

And a replacement of

SELECT ${q} LIMIT ${t}

For example

This is a really simplistic case, and it won't handle subqueries well. You could skip the presence of ORDER BY in the find-str...

It's not really a simple thing though, parsing a programming language with regex is like trying to use it for parsing html. You could get more involved with repeated replacements and negative lookaheads to avoid matching stuff you've already replaced.. But tbh it might be better to look at parsing the SQL properly if you're looking at a complex scenario

edited Oct 24 '20 at 16:16

answered Oct 24 '20 at 14:35

Caius Jard

47,616
4
34
62

This will do the job. Thank you very much Caius! – Osi Oct 25 '20 at 17:38
You're most welcome! If you click the grey check/tick mark on this answer to turn it green it will indicate on the questions dashboard that this question has been answered. – Caius Jard Oct 25 '20 at 17:51

Regexp convert Sql Server TOP syntax with Postgresql LIMIT syntax

1 Answers1