Formatting select top 100 ....

[kadriizer]

Hi all,
Is there any way to format a "select top 100 a,b,c from table" like following first one? It's formatted like second one!

SELECT TOP 100
A,
B,
C
FROM
TABLE

SELECT
TOP 1 A,
B,
C
FROM
TABLE

[nene]

I assume this is Transact-SQL. Unfortunately there's currently no way to format it like so. Essentially it's similar to the problem of formatting SELECT DISTINCT ON in PostgreSQL (#144). It's also not easy to implement a fix for it and because it's a very non-standard syntax, it's very low priority.

If the SQL is under your control, perhaps use the standard FETCH FIRST 100 ROWS ONLY syntax.

[kadriizer]

I solved this issue by a simple trick. Thank you any way ;)
Kadri

[karlhorky]

@kadriizer what was your simple trick? It may be helpful for others :)

[kadriizer]

Actualy, I've been developing a new language (firebird). So needed such "select first ... a,b,c from table". And I solved by writing following codes... Remeber that, all of these for only testing. After completting firebird language for sql-formatter, I will share for all which needs ;)

/* ./src/languages/firebird.formatter.ts */
....
const reservedSelect = expandPhrases(['[FOR] SELECT [FIRST 0 | DISTINCT]']);
....

/* test.js */

.....
function extractFirstNumber(query) {
// SELECT FIRST <sayı> yakalamak için regex
// Case-insensitive ve boşluklara toleranslı
const regex = /SELECT\s+FIRST\s+(\d+)/i;
const match = query.match(regex);
if (match) {
return parseInt(match[1], 10); // Sayıyı integer olarak döndür
}
return null; // FIRST yoksa null
}

function replaceFirstWithPlaceholder(query) {
// FIRST <sayı> -> FIRST #
return query.replace(/(SELECT\s+FIRST\s+)\d+/i, '$10');
}

function restoreFirstNumber(query, number) {
// FIRST # -> FIRST <sayı>
return query.replace(/(SELECT\s+FIRST\s+)0/i, $1${number});
}

....