TextExpander and SQL

TextExpander Talk
1

I’m not sure about everyone else, but whenever I need to run a database query, I can never seem to remember the exact SQL syntax. To make things easier, I’ve started saving my queries as TextExpander snippets, and it’s been a game-changer. Here are a few of the ones I use regularly—hopefully they’ll be helpful to others as well:

abbr: ;sqldate

date_field >= DATEADD(Day ,-30, current_date)
date_field >= '1/1/2025'

abbr: ;sqlcast
CAST(date_field AS DATE) as date

abbr: ;sqlqual

select row_number() over(partition by KEY order by FIELD asc) as rn
from database_name
qualify rn = 1

abbr ;sqlarray
where field in (1,2,3)

abbr ;sqlstring
CHARINDEX('string', Column1) > 0

abbr: ;sqlcreate
create or replace view table_name as ()

Anyone else have cool sql snippets?

3 Likes
2

@roger Love the use cases here for using TextExpander to help with repetitive SQL commands and queries. When I joined TextExpander and discovered this use case in my role as the data analyst, it too improved my SQL productivity. Here are a few of my saved Snippets:

abbr ;sqlepoc (inserts the command on how to change dates stored in EPOC format to calendar format)
dateadd('ms', <epoc column name> ,'1970-01-01')

abbr ;sqlgrant
grant <database privilege> on <db object> in <schema name> to role <role name>;
This has been a time saver not having to search google for the syntax. At the same time, I can create drop down lists or user fill-ins to enter the elements within the <>.

Along with the personal SQL snippets, we created Shared Team SQL snippets to support our finance and customer facing teams. Creating SQL report snippet that our teams can expand allows them to self-serve on specific requests without the involvement of the data or engineering teams. These generally start as a one-off request and when the usage meets a threshold, they become candidates for a formal dashboard or report in our BI tools.

Looking back on my data analyst career, I wish I had found TextExpander sooner. The number of hours I could have saved in sharing and expanding existing SQL snippets vs having to dig through GitHub repositories, looking through old code or asking co-workers to copy/paste and share their code would have been immeasurable.

3 Likes
3

This is a great idea. I used some of them to create my own. I then started to insert pop up menus and values for the common variables that I use. Such as the Dateadd. Used a menu to display all the valid increments that can be used such as Day, Hour, Month etc.

This is a wicked time saver.

2 Likes
4

Love the extra popup menu variables - that is a great idea. I will add that to my snippets.

1 Like