Regular Expressions in Oracle SQL

I Know Regular Expressions

For a project I needed to change the picture URL’s of all users. Fortunately Oracle SQL supports regular expressions making this problem much easier to solve.

SQL to select pictures to change:

SELECT picture FROM users
WHERE REGEXP_LIKE(picture, '^graph\.facebook\.com.*?type=square'); 

SQL to update pictures from type=square to type=large: 

UPDATE users SET picture =
REGEXP_REPLACE(picture,
               '^(graph\.facebook\.com.*?type=)square$', '\1large')
WHERE REGEXP_LIKE(picture, '^graph\.facebook\.com.*?type=square$');

SQL to select updated pictures:

SELECT user_id, picture FROM users
WHERE REGEXP_LIKE(picture, '^graph\.facebook\.com.*?type=large$');

For more information please read Using Regular Expressions in Oracle Database.

Leave a Reply

Your email address will not be published. Required fields are marked *