python to extract/manipulate text data DIRECTLY with MS SQL

Question

Currently I use basic SQL's string functions (PATINDEX and SUSTR) to extract text data.

From what I understand, MS SQL does not have full implementation of regex which makes it very difficult to extract data from string.

Some people suggested Python because it's more suited for text string manipulation.

So my question has two parts:

1. Is there SQL capabilities that I am not aware of? I would prefer to stay native with SQL.
2. Is there SQL capabilities that I am not aware of? I would prefer to stay native with SQL.
   How easy or difficult is it to go with Python route? And can I read/write directly to SQL?

Answer 1

If you are on SQL 2017, you can use Python from within SQL Server. It is an optional install, though, so you may not have it. But you can re-run Setup to add it. Once you have it place, you need to run

sp_configure 'external scripts enabled', 1
RECONFIGURE

Here is a sample script where I use Python to remove all non-digits from a script:

USE tempdb
go
CREATE TABLE testtabell(id       int           NOT NULL PRIMARY KEY, 
                        litetext varchar(20)   NOT NULL)

INSERT testtabell(id, litetext)
   VALUES(1, 'ABC7890DEF'), (2, '123 00 00'), (3, 'Ingasiffror'), (4, '1234567890')

EXEC sp_execute_external_script @language = N'Python', 
     @script = N'import re, pandas
Ret = InputDataSet
Ret["litetext"] = pandas.Series([re.sub("[^0-9]", "", i) for i in Ret["litetext"]])
', 
     @input_data_1 = N'SELECT id, litetext FROM testtabell', 
     @output_data_1_name = N'Ret'
     WITH RESULT SETS ((id       int, 
                        litetext varchar(10)))
go
DROP TABLE testtabell