Searching for permutations of empty string
Sometimes when you upload data to Domo the space, new line, or carriage return comes in 'weird' making them difficult to excise from your data
use
- STR_TO_CHARNAMES
- STR_TO_HEX
Regex Replace in MySQL 5.6
MySQL 5.6 doesn't have a regex replace function. Here's a function that will do it for you.
https://stackoverflow.com/questions/986826/how-to-do-a-regular-expression-replace-in-mysql
call the procedure
SELECT regex_replace('[^a-zA-Z0-9/ \\-]', '' ,`Row_Name`) as test
create the function
CREATE FUNCTION `regex_replace`(pattern VARCHAR(1000),replacement VARCHAR(1000),original VARCHAR(1000))
RETURNS VARCHAR(1000)
DETERMINISTIC
BEGIN
DECLARE temp VARCHAR(1000);
DECLARE ch VARCHAR(1);
DECLARE i INT;
SET i = 1;
SET temp = '';
IF original REGEXP pattern THEN
loop_label: LOOP
IF i>CHAR_LENGTH(original) THEN
LEAVE loop_label;
END IF;
SET ch = SUBSTRING(original,i,1);
IF NOT ch REGEXP pattern THEN
SET temp = CONCAT(temp,ch);
ELSE
SET temp = CONCAT(temp,replacement);
END IF;
SET i=i+1;
END LOOP;
ELSE
SET temp = original;
END IF;
RETURN temp;
END
Regex in MagicETL 1.0 using Capture Groups
- Then, type or copy the following regular expression into the box labeled Enter a term to search for: **^http[s]?://([a-zA-Z0-9-.]).$**The ^ symbol indicates you want the pattern to start with what comes after this. The start of any matching value will have to be "http", followed by an optional "s", since web addresses may or may not have an "s". The "?" indicates that what preceeds it is optional. The value must then have "://".Now we use parentheses to indicate that we want to remember or capture a section of the value for future use. This is called a capture group.Inside the parentheses you have indicated that the URL can have any lower case characters, any upper case characters, any digits 0-9, a hyphen, or a period. It will then look for any number of those characters together.After the capture group, the pattern indicates that there may or may not be other characters before the text value ends. The $ indicates that in order to be considered a match, nothing else can come after the part of the text that matches the pattern. In other words, because of the "^" and "$" characters, the pattern provided must match the entire value in the column, not just a part of it.
In the Replace found term with box, type "$1". This $1 references the first capture group in your pattern. Using it here indicates that you want to replace the value that matches the entire regex pattern with what you've captured in the capture group. In this example, that means replacing the entire URL with just the domain.
