Discover and Substitute Textual content with SQL Common Expressions in Rockset



In our first weblog, we used an everyday expression to interchange the quotes in genres. Afterward, we have been capable of UNNEST() the JSON object. We’ll be working with the identical knowledge set on this weblog

In our knowledge:

Embedded content material:

there’s a JSON string that’s known as spoken_languages, and it’s formatted equally to genres:

[ { "spoken_languages": "[{'iso_639_1': 'fr', 'name': 'Français'}]" }]

Assuming every little thing is constant, we are able to simply write the SQL assertion just like what we wrote for genres— proper?

Find and replace text using regular expression in SQL - drums

Flawed ⛔️

We truly get a parsing error:
json parse error on line 0 close to `x9akai”}, {“iso_’: unknown escape 120 in string

So, we type of know what the wrongdoer is right here, however we don’t know if there are going to be extra parsing errors. Let’s go forward and debug this, so we are able to get this SQL assertion working!

We are able to truly use REGEXP_LIKE() to see what precisely is inflicting the error:

    commons.TwtichMovies t
WHERE REGEXP_LIKE(t.spoken_languages,'x9akai')

This can be a pattern of what we get again:

Find and replace text using regular expression in SQL - sample query

That is nice— it appears to be like just like the are literally inflicting some points. We are able to use REGEXP_REPLACE() to interchange these slashes with an empty string:

SELECT (REGEXP_REPLACE('Lietuvix9akai', '',''));

Now— to place all of it collectively: How will we UNNEST() spoken_languages and repair the two points at hand (the string format and the double slashes)?


 SELCT REGEXP_REPLACE(REGEXP_REPLACE('[{''iso_639_1'': ''lt'', ''name'':''Lietuvix9akai''}]', '''', '"'), '', '');

I’m certain you possibly can take it to the end line from right here, however simply in case, you possibly can watch this youtube hyperlink down beneath to catch the total replay!

Embedded content material:

TLDR: you could find all of the sources you want within the developer nook.