So, the calculation is modified only slightly to account for the = or # characters.
REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE([Unclear Data],CHAR(10),''),'(\d{4}:)',''),'<.*?>|&.*?;|[=]|[#]',' ')And it returns this when I modified the original data set:
As to usage of CHAR(10) and '(\d{4}:) - you have at least one occurrence where there's a carriage return in the string but not all of the strings have them. CHAR(10) is the code that represents a carriage return. So, the first (most inner) REGEXP_REPLACE looks for that carriage return (which is not visible) and eliminates it so that the end part of the calculation works. For the capture group of (\d{4}:) in the second REGEXP_REPLACE it is used to remove those 4-digit (not letters) instances that you had in the very first row of the data example. There were a number of items like 1234: or 5678: etc. The capture group looks for just 4 digits, starting from the colon and then removes them. The very last REGEXP_REPLACE, the outer one, is doing all the heavy lifting. In Regex, the pipe | symbol is an 'or' operator. So that last piece '<.*?>|&.*?;|[=]|[#]' has four different patterns for the regex engine to look for and eliminate. The first one looks for HTML tags, the second one looks for any string values between the & and ; the last two are your most recent need and it's just simply [=] | [#] Hope that helps! Best, Don
9 answers
