Is there a way to generate table aliases in TSQL statements using SQL snippets in Azure Data Studio?

Question

In a user snippets the following work,

SELECT * FROM ${1:TableName} as ${2|a,b,c,d,e,f|}

but it only presents you with a list of the 6 letters ( a to f) for an alias like this:

SELECT * FROM Currency as a

I want the snippet to use the first 4 characters of the table name as an alias:

SELECT * FROM Currency as curr

Is there a way to make this happen in a user snippet?

score 1 · Accepted Answer · answered Aug 24 '23 at 11:54

You can create user snippet to generate first four letters of table name as table name alias as follows:

Add below snippet to the created user snippet:

{
    "Create Select Statement": {
        "prefix": "sqlselect",
        "body": [
            "SELECT * FROM ${1:TableName} as ${1/(.{4}).*/$1/}"
        ],
        "description": "Create a SELECT statement for a table"
    }
}

In this snippet, ${1/(.{4}).*/$1/} is using a regular expression transform to capture the first 4 characters of the table name and apply them as the alias. The expression works as follows:

(.{4}): This captures the first 4 characters of the table name.
.*: This matches any remaining characters in the table name.
$1: This references the captured group from the first part of the expression, which are the first 4 characters of the table name.

I formatted above snippet as document and opened new query tab typed sqlselect and pressed tab I got below script:

SELECT  *  FROM TableName as TableName

Entered the table name and pressed tab I got required output as mentioned below:

SELECT  *  FROM student as stud

enter image description here

Nice one, thank you! Additionally, I wanted to make the alias lowercase, and use this:" SELECT * FROM ${1:TableName} as ${1/(.{3}).*/${1:/downcase}/} — callisto, Aug 24 '23 at 16:02

Is there a way to generate table aliases in TSQL statements using SQL snippets in Azure Data Studio?

1 Answers1