Need help in Regex

Question

Hi All,

In my dataset the rows are like (Kindly find the attached file)

I wanted to fetch the value in double quotes starting with win_64 ==== win_64_client="4" === The value will be 4 in new column

When I'm applying Regex parse or Regex_Replace formula it's working fine in Alteryx.

.+\w{3}\d+_\w+=.+(\d+).+

REGEX_REPLACE([DEVICE_INFO], ".+\w{3}\d+_\w+=.+(\d+).+", "$1")

But when I'm applying the same regex expression in Snowflake while connecting the desired result from Snowflake database, I'm getting Null values.

select db_userid, device_info,

regexp_substr(device_info, '.+\w{3}\d+_\w+=.+(\d+).+') as RESULT

FROM ZOOM_DATA_GO.DYNAMODB_ABCD

where device_info like '%win_64_client%'

limit 10;

Can anyone please help me with the correct syntax? Need guidance.

Rows.txt

RE5260 · Answer

Amol_Telore

Found error -

100048 (2201B): Invalid regular expression: '^.*?(?<=win_64_client=")(\d+)".*$', no argument for repetition operator: ?

Amol_Telore · Answer

This is SQL equivalent of REGEX_REPLACE in Alteryx.

select db_userid, device_info,

REGEXP_REPLACE(device_info, '^.*?(?<=win_64_client=")(\d+)".*$', '\1 ') as RESULT

FROM ZOOM_DATA_GO.DYNAMODB_ABCD

where device_info like '%win_64_client%'

limit 10;

Amol_Telore · Answer

Hey @RE5260

You can use below Regex expression to parse numeric value after win_65_client and between double quotes. this works in Alteryx, have not tried on SQL. you can give it a try once.

^.*?(?<=win_64_client=")(\d+)".*$