How to Write SQL Using Speech Recognition

Can I write SQL using speech recognition?

Yes. SQL is well-suited to speech recognition (as well-suited as a programming language can be, that is), given it's limited vocabulary and sentence-like structure. Aside from formatting the SQL so that it looks nice, I can dictate it much faster than typing. Dictating code isn't for everyone, however. It can be quite frustrating in the beginning. The people who try this and stick with it will probably be those who have no other choice.

I use Dragon NaturallySpeaking 10 Professional. The Professional version has the tools that are needed to create a custom vocabulary like this. Version 9 should work fine, also. It's expensive, so try to get the company you work for to pay for it if possible. Get a decent headset microphone also. The one that comes with NaturallySpeaking isn't good enough (but you may want to try it first to see if it works for you). KnowBrainer is a good place for microphone recommendations.

2009-01-05 Update: I have added tips below specific to dictating in SQL Server Management Studio.

2012-01-04 Update: I have been keeping track of Microsoft's WSR for quite a while now, hoping tools would be added to easily create a completely custom vocabulary from scratch like I am doing in this tutorial with NaturallySpeaking. Unfortunately, it appears that this can only be done through the API (SAPI). I don't have the time to write that code, so I will continue to use NaturallySpeaking to write code until something better comes along.

Preparation

Clean up your database names and code

Dictating "SELECT PT_17, PT_28, PT_29 FROM HIK.dbo.PATINFO" would be a pain in the butt, but I guess it would be possible. You would have to set a lot of pronunciations, since NaturallySpeaking would have no idea how "PT_17" would sound. This would be preferable for dictation:

SELECT Patient.FirstName, Patient.MiddleName, Patient.LastName FROM Claim.dbo.Patient AS Patient WHERE Patient.LastName LIKE '%smith%'

I switched to my TSQL vocabulary to dictate the above statement. Everything up to the LIKE statement is spoken just as it appears. '%smith%' was dictated as "open-single-quote percent-sign sierra mike india tango hotel percent-sign close-single-quote [PAUSE] compound-that". Using consistent table aliases and always preceding fields with them helps improve accuracy, since NaturallySpeaking keeps statistics of how often one word appears near another.

Create a word list of SQL keywords

Put one word on each line. You can optionally follow a word with a backslash (\) and a pronunciation. NaturallySpeaking uses a small backup dictionary of words to determine the pronunciation of words you add to a vocabulary, so it has no problem figuring out how SELECT, FROM, and WHERE are pronounced. It can sometimes figure out a compound word, and it makes its best guess for something like XACT_ABORT. I would provide pronunciations for cases like these. The database you use will determine what words the list contains - check your documentation for a list of keywords. Your list will look something like this, but be much longer.

SELECT
WHERE
FROM
XACT_ABORT\exact-abort
MAXDOP
NOLOCK\no-lock
LEN
RETURNS
CURSOR
MONEY

Also add these words

\New-Line
\New-Paragraph
\All-Caps
\All-Caps-On
\All-Caps-Off
\Cap
\Caps-On
\Caps-Off
\No-Caps
\No-Caps-On
\No-Caps-Off
\No-Space
\No-Space-On
\No-Space-Off
\space-bar
\tab-key
a\alpha
b\bravo
c\charlie
d\delta
e\echo
f\foxtrot
g\golf
h\hotel
i\india
j\juliet
k\kilo
l\lima
m\mike
n\november
o\oscar
p\papa
q\quebec
r\romeo
s\sierra
t\tango
u\uniform
v\victor
w\whiskey
x\xray
y\yankee
z\zulu
PM
AM
one
two
three
four
five
six
seven
eight
nine
ten
eleven
twelve
thirteen
fourteen
fifteen
sixteen
seventeen
eighteen
nineteen
twenty
thirty
fourty
fifty
sixty
seventy
eighty
ninety
hundred
thousand
million
billion
trillion

Keep this list around, since you'll probably modify it several times and re-create your vocabulary to get it the way you like it.

Create a word list of your database object names

This is how I do it in SQL Server:

SELECT DISTINCT * FROM 
(
SELECT DISTINCT [name] FROM Database1.[dbo].[sysobjects] WHERE xtype not IN ('F', 'S', 'PK', 'D', 'UQ') 
UNION 
SELECT DISTINCT column_name AS [name] FROM Database1.information_schema.[columns]
UNION
SELECT DISTINCT [name] FROM Database2.[dbo].[sysobjects] WHERE xtype not IN ('F', 'S', 'PK', 'D', 'UQ') 
UNION 
SELECT DISTINCT column_name AS [name] FROM Database2.information_schema.[columns]
...
) AS UnionTable

Copy and paste the results into a text file.

Create pronunciations for your database object names

Use the same format for pronunciations as listed above. An easy way to create these is to use a regex search and replace function. In SQL Server Management Studio or Visual Studio the following (non-standard) regex will create pronunciations for two word mixed case names.

Find: ^{[A-Z][a-z]+}{[A-Z][a-z]+}$
Replace: \0\\\1-\2

Review the pronunciations and clean up anything that doesn't look right. For acronyms, ASP becomes `A.S.P.'. Keep this list around, as well. If you decide to make vocabularies for other programming languages, you will probably include these words if you're a database developer.

Create a text document that contains all of your SQL code (views, procedures, etc.)

SQL Server:

SELECT * FROM Database1.dbo.[View] UNION SELECT * FROM Database1.dbo.Routine UNION
SELECT * FROM Database2.dbo.[View] UNION SELECT * FROM Database2.dbo.Routine 
...
ORDER BY [Name]

Remove comments and literal strings. Regex search and replace works well for this.

Build your vocabulary

Install NaturallySpeaking and create a new user if you have not already.

Create a new vocabulary

Click on "NaturallySpeaking | Manage Vocabularies...". Click New. Name the vocabulary something appropriate, such as "SQL". Base it on "Base General - Empty Dictation". When it asks you if you want to scan your email or documents, click cancel.

Import words

Click "Words | Import". Add the two word lists you created and import them.

Adapt to writing style

Click "Tools | Accuracy Center". Click "Add words from your documents to the vocabulary". Use the default settings, and select the document you created which contains your code.

Try dictating some SQL

The first thing you'll probably want to dictate is a select statement. Keep in mind that SELECT is what you use to begin a command in NaturallySpeaking that selects text. Because of this, you'll want to say "Cap" before dictating it so NaturallySpeaking doesn't get confused. That's it. Well, at least enough to get you started. Modify your word lists, pronunciations, and word properties as needed. There are other things you can do to increase accuracy and the speed at which you can dictate. As I think of them, I will edit this post and add them here.

Tips for dictating into SQL Server Management Studio

If you dictate into SQL Server Management Studio, you may notice very slow performance. Try the following to alleviate this:

Turn off all toolbars (create macros
to access commonly used
functionality)
Keep as few panes and
documents open as possible
Keep only one database open at a time
Hide search results after you're done
with them (Ctrl+R)
If all else
fails, close and reopen management
studio
Display the tab stops in the edit window to make it easier to format your SQL.

Query Analyzer from SQL Server 2000 does not have these issues.

Insert python (voice recognition text) to SQL Server database

To join/concatenate string from variable you have to use +

sqlcommand = " insert into yourtext values ('" + text + "')"

but it would be safer to use ? and send text as argument in execute

execute("INSERT INTO yourtext VALUES (?)", (text,) )

BTW: execute() needs tuple with arguments - even if you have only one argument. And it needs comma in (text,) to create tuple. Parenthesis () doesn't create tuple. They only separate text, from other comma in this line.

Voice Recognition Software For Developers

It's out there, and it works...

There are quite a few speech recognition programs out there, of which Dragon NaturallySpeaking is, I think, one of the most widely used ones. I've used it myself, and have been impressed with its quality. That being a couple of years ago, I guess things have improved even further by now.

...but it ain't easy...

Even though it works amazingly well, I won't say it's an easy solution. It takes time to train the program, and even then, it'll make mistakes. It's painstakingly slow compared to typing, so I had to keep saying to myself "Don't grab the keyboard, don't grab the keyboard, ..." (after which I'd grab the keyboard anyway). I myself tend to mumble a bit, which didn't make things much better, either ;-). Especially the first weeks can be frustrating. You can even get voice-related problems if you strain your voice too much.

...especially for programmers!

All in all, it's certainly a workable solution for people writing normal text/prose. As a programmer, you're in a completely different realm, for which there are no real solutions. Things might have changed by now, but I'd be surprised if they have.

What's the problem? Most SR software is built to recognize normal language. Programmers write very cryptic stuff, and it's hard, if not impossible, to find software that does the conversion between normal language and code. For example, how would you dictate:

if (somevar == 'a')
{
   print('You pressed a!');
}

Using the commands in your average SR program, this is a huge pain: "if space left bracket equal sign equal sign apostrophe spell a apostrophe ...". And I'm not even talking about navigating your code. Ever noticed how much you're using the keyboard while programming, and how different that usage is from how a 'normal' user uses the keyboard?

How to make the best of it

Thus far, I've only worked with Dragon NaturallySpeaking (DNS), so I can only speak for that product. There are some interesting add-ons and websites targeted for people like programmers:

Vocola is an unofficial plugin that allows you to easily add your own commands to DNS. I found it essential, basically. You'll also be able to find command sets written by other programmers, for e.g. navigating code. It's based on a software package written in Python, so there are also some more advanced and fancy packages around. Also check out Vocola's Resources page. (Warning: when I used it, there were some problems with installing Vocola; check out the newsgroup below for info!)
SpeechComputing.com is a forum/newsgroup with lots of interesting discussions. A good place to start.

Closing remarks

It seems that the best solution to this problem is, really:

Find ways around actual coding.
Try to recover. I'm somewhat reluctant to recommend this book, but it seems to work amazingly well for people with RSI/carpal tunnel and other chronic pain issues: J.E. Sarno, Mindbody prescription. I'm working with it right now, and I think it's definitely worth reading.

What is the best practice to insert a row after some consecutive rows in a table?

You can use GROUPING SETS for this

SELECT
  REGION = CASE WHEN GROUPING(REGION) = 1 THEN 'Total' ELSE REGION END,
  CATEGORY,
  SUBCATEGORY,
  ItemName,
  ItemViews = SUM(ItemViews),
  sales = SUM(sales),
  ItemViewspercentage = FORMAT(SUM(ItemViews) * 1.0 / SUM(sales), 'P2')
FROM YourTable t
GROUP BY GROUPING SETS (
  (CATEGORY, SUBCATEGORY, ItemName, REGION),
  (CATEGORY, SUBCATEGORY, ItemName)
);

db<>fiddle

How to perform regular expressions on a Speech Recognition transcript?

for me I just put the speakBackToMe(); line below console.log(transcript); within the result event listener block and it worked

recognition.addEventListener('result', e => {
     transcript = Array.from(e.results)
        .map(result => result[0])
        .map(result => result.transcript)
        .join('');
    console.log(transcript);
    speakBackToMe();
});