Comments
Sort by recent activity
All of these fail to format and error for me: Azure SQL Database, compatibility level 170 SQL Prompt 11.3.10.22862 SELECT
IIF(CURRENT_DATE=CURRENT_DATE AND is_ms_shipped=1,1,0) AS [is_ms_shipped]
FROM sys.objects AS [o]
SELECT
REGEXP_REPLACE ([o].[type], 'USER_|SYSTEM_', '') AS [regexp_replace]
FROM [sys].[objects] AS [o];
SELECT
REGEXP_SUBSTR(CONCAT([o].[schema_id], ':', [o].[name]), '\\d+', 1, 1) AS [regexp_substr]
FROM sys.objects AS [o];
SELECT
REGEXP_INSTR(CONCAT([o].[name], '_', [o].[object_id]), '\\d+', 1, 1) AS [regexp_instr]
FROM sys.objects AS [o];
SELECT
REGEXP_COUNT([o].[name], '_') AS [regexp_count]
FROM sys.objects AS [o];
SELECT
CASE WHEN REGEXP_LIKE([o].[name], '^[A-Za-z]') THEN 1 ELSE 0 END AS [regexp_like]
FROM sys.objects AS [o];
SELECT
[m].[match_value] AS [regexp_matches]
FROM sys.objects AS [o]
CROSS APPLY REGEXP_MATCHES(CONCAT('#', [o].[name], ' #AzureSQL'), '#\\w+') AS [m];
SELECT
[s].[Value] AS [regexp_split_to_table]
FROM sys.objects AS [o] CROSS APPLY REGEXP_SPLIT_TO_TABLE([o].[name], '[,;|]') AS [s]; / comments
All of these fail to format and error for me:Azure SQL Database, compatibility level 170SQL Prompt 11.3.10.22862 SELECT
IIF(CURRENT_DATE=CURRENT_DATE AND is_ms_shipped=1,1,0) AS [is_ms_shipped...
Also seeing the same behaviour with REGEXP_LIKE: SELECT
*
, REGEXP_REPLACE ([o].[type_desc], 'SYSTEM_|USER_', '') AS [type_desc]
FROM [sys].[objects] AS [o]; Using [type_desc2] formats the code without error, but then SQL Prompt puts REGEXP_REPLACE in square brackets, which is not valid SQL, so fails to execute. / comments
Also seeing the same behaviour with REGEXP_LIKE:SELECT
*
, REGEXP_REPLACE ([o].[type_desc], 'SYSTEM_|USER_', '') AS [type_desc]
FROM [sys].[objects] AS [o];Using [type_desc2] formats the code...
Seems to be CURRENT_DATE it doesn't like, this works fine: SELECT * , CAST (IIF([o].[is_ms_shipped] = 0 AND DATEDIFF (DAY, CAST (SYSUTCDATETIME () AS DATE), [o].[create_date]) BETWEEN 0 AND 7, 1, [o].[is_ms_shipped]) AS [BIT]) AS [is_ms_shipped] FROM [sys].[objects] AS [o]; I've also noticed that using CURRENT_DATE breaks intellisense, not sure if that SQL Prompt or SSMS? / comments
Seems to be CURRENT_DATE it doesn't like, this works fine:SELECT * , CAST (IIF([o].[is_ms_shipped] = 0 AND DATEDIFF (DAY, CAST (SYSUTCDATETIME () AS DATE), [o].[create_date]) BETWEEN 0 AND 7, 1, ...
The problem I have is that when I format the code above it changes . . .AS [is_ms_shipped] to …AS [o].[is_ms_shipped], which isn't valid SQL and causes SQL Prompt to fail with an incorrect syntax error. If I change my alias to something different to the name of the column used inside the IIF, e.g. [is_ms_shipped2], then SQL Prompt can format it just fine. I've only noticed it happening with the combination of code above. / comments
The problem I have is that when I format the code above it changes . . .AS [is_ms_shipped] to …AS [o].[is_ms_shipped], which isn't valid SQL and causes SQL Prompt to fail with an incorrect syntax e...