Regular expression to extract all words starting with colon

Question

I would like to use a regular expression to extract "bind variable" parameters from a string that contains a SQL statement. In Oracle, the parameters are prefixed with a colon.

For example, like this:

SELECT * FROM employee WHERE name = :variable1 OR empno = :variable2

Can I use a regular expression to extract "variable1" and "variable2" from the string? That is, get all words that start with colon and end with space, comma, or the end of the string.

(I don't care if I get the same name multiple times if the same variable has been used several times in the SQL statement; I can sort that out later.)

Answer 1

This might work:

:\w+

This just means "a colon, followed by one or more word-class characters".

This obviously assumes you have a POSIX-compliant regular expression system, that supports the word-class syntax.

Of course, this only matches a single such reference. To get both, and skip the noise, something like this should work:

(:\w+).+(:\w+)

Answer 2

If your regex parser supports word boundaries,

:[a-zA-Z_0-9]\b

Answer 3

Try the following:

sed -e 's/[ ,]/\\n/g' yourFile.sql | grep '^:.*$' | sort | uniq

assuming your SQL is in a file called "yourFile.sql".

This should give a list of variables with no duplicates.

Answer 4

For being able to handle such an easy case by yourself you should have a look at regex quickstart.

For the meantime use:

:\w+