R Services on SQL Server 2016+ (and Regular Expressions)

One way to get regular expression support in SQL Server is to use the language "R".

R can be used if you have R Services installed. It's available in SQL Server 2016 onward. (And Python services are in SQL Server 2017 onward)

if the services are installed, you also need to configure SQL Server it allow external scripts to be enabled.

Check if currently enabled

EXEC sp_configure  'external scripts enabled'
name minimum maximum config_value run_value
external scripts enabled 0 1 1 0

The above means that it is enabled.

However, because I didn't restart SQL Server after enabling it, it still doesn't work.

Enable it

sp_configure 'external scripts enabled', 1;

Test it

EXEC sp_execute_external_script  @language =N'R',
 OutputDataSet <- InputDataSet;
@input_data_1 =N'SELECT 1 AS hello'
WITH RESULT SETS (([hello] int not null));