CODEWITHSUNDEEP
azure-sql-databaseazure-synapse
Shankar
Shankar

Reputation: 601

regexp_replace in Azure SQL DW

Is there any replacement built in function for REGEXP_REPLACE in Azure SQL DW as in Oracle / or any other Relational databases?

I need remove the GUID values and any Alphanumeric values from the URL. Example given below. Is there any way it can be achieved easily in azure SQLDW ? if not in SQL DW, then atleast in Azure SQLDB?

Input

/my-account/premises/001A4BF58F8C1EE1ACE8ED6A65698305/accounts/overview

/my-account/001A4BF5891C1ED1A5F27409BC0A1D02/accounts/851008500240-1602-1512164572/tariff

/my-account/premises/001A4BF5891C1EE1A0B1190619534001/accounts/85-0000286922

/my-account/premises/001A4BF5891C1ED1A5F2C3BD506D0E07/accounts/overview

Output_expected

/my-account/premises/accounts/overview

/my-account/accounts/tariff

/my-account/premises//accounts/

/my-account/premises/accounts/overview

Upvotes: 3

Views: 3510

Answers (1)

wBob
wBob

Reputation: 14379

Azure SQL Data Warehouse does not include support for Regular Expressions.

What you could do is use a federated query from U-SQL, ie within a U-SQL script get data from your warehouse, transform it using RegEx then output as a flatfile. Import that flatfile into your data warehouse using Polybase. U-SQL ADLA does not yet have the ability to write directly to SQL DW.

Sample script:

USING rx = System.Text.RegularExpressions.Regex;

/*!!TODO do federated query to Azure SQL Data Warehouse here instead
@input =
    SELECT *
    FROM EXTERNAL MyAzureSQLDWDataSource LOCATION "dbo.yourTable";
*/ 
@input = SELECT *
        FROM (
        VALUES
            ( "/my-account/premises/001A4BF58F8C1EE1ACE8ED6A65698305/accounts/overview" ),
            ( "/my-account/001A4BF5891C1ED1A5F27409BC0A1D02/accounts/851008500240-1602-1512164572/tariff" ),
            ( "/my-account/premises/001A4BF5891C1EE1A0B1190619534001/accounts/85-0000286922" ),
            ( "/my-account/premises/001A4BF5891C1ED1A5F2C3BD506D0E07/accounts/overview" )
        ) AS t( yourPath );


@output =
    SELECT rx.Replace(yourPath, @"/([0-9]|[A-F]|-){13,32}", "") AS cleanPath
    FROM @input;


OUTPUT @output
TO "/output/output.csv"
USING Outputters.Csv(quoting : false);

My results:

My results

Read more about federated queries here. RegEx put together with help from https://regex101.com/.

Upvotes: 4

Related Questions