Regexp

Every 4 months or so I need a simple way to split a string (VARCHAR2) into elements, where the elements are separated with some fixed value (a comma, a colon, or perhaps a longer string). Since my short-term memory is too short0, I figured I should make a reminder here. Of course, you’ll find this on Stackoverflow as well. There is this function in APEX, which is usually1 available for you in the database, even if you are not using APEX. Here is a short demo: ...

Yesterday I was importing some data I downloaded. When creating a new table I tried to convert two columns with latitude and longitude, stored as VARCHAR2, to numbers (in order to use them in the SDO_GEOMETRY constructor). The create table as select (CTAS) statement failed with ORA-01722. Problem is it does not tell you which line or what value is the offender. You may turn on some tracing with event 1722, but using regular expression is much easier: ...

Three family members invited me to play Wordfeud with them this Christmas. At the end of such a game losing badly to my sister-in-law and with only a few letters left I was thinking about how I could find candidate words using regular expression in SQL. First I found a list of Norwegian words on the net. I should have taken the effort to get hold of the same word list as Wordfeud is using. The list was loaded into a table using SQL Developer. ...