Need regex for SQL to extract substring from a long string

Question

I have the below string in my database (SnowFlake)

:display_redemption_mark: 0
:redeemable_properties: '1205,1206,1207,1208,1209,1210,1211,1212,1213,1214,1215,1216,1217,1218,1225,1227,1230,1231,1232,1239,1242,1244,1249,1250,1254,1281,1282,1283,1284,1285,1286,1287,1288,1289,1290,1291,1292,1293,1294,1296,1297,1240,1247,1255'
:redemption_meta_keys: ''
:max_redemptions_in_transaction: 0
:allow_multiple_redemption_on_item: false
:allow_qualifying_items_reused: false

I want to extract the value of the redeemable_properties which in above case

1205,1206,1207,1208,1209,1210,1211,1212,1213,1214,1215,1216,1217,1218,1225,1227,1230,1231,1232,1239,1242,1244,1249,1250,1254,1281,1282,1283,1284,1285,1286,1287,1288,1289,1290,1291,1292,1293,1294,1296,1297,1240,1247,1255

I want to exclude single quote, and double quote as the whole value is a string and is stored in a column of the table. I am creating a new column with the extracted value.

Gokhan Atil · Accepted Answer · 2023-01-09 16:48:17Z

1

Can you try this one?

select trim( regexp_substr( your_column, '.*:redeemable_properties:([^:]*):',1,1,'e' ), '''" ' ) from your_table;

answered Jan 9, 2023 at 16:48

Gokhan Atil

10.6k2 gold badges13 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Need regex for SQL to extract substring from a long string

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related