I am working on a job to extract the version numbers from a string and map it to the corresponding version number column. Mapping in the sense just putting 1 in that corresponding version number column (v1.1, v1.2.5, v1.3 below). If the same version is there in 2 places in the string, then the column should be made as 2.
Below are the sample records for this logic on how it should look:
It looked simple, but I cant think of any logic to do this. I used search_replace to remove those alphabets and spaces just to make it easier to think of any logic. By the way, I am using BODS 3.1 version.
Any solution regarding this is highly appreciated. Hope the above explanation is clear. I searched the forum topics but does not have what I am looking for.
you could write a custom function where inside a while loop you check for the index() of each version, replace_substr_ext for each occurance … until there are no more found. Increment a version counter for each index found.
this would be pretty easy as you know exactly what you’re searching for.
[edit]replace_substr_ext() does not appear to work for combinations of the same character. Guess I’ll have to write one of those too. [/edit]
If you were to use a SQL transform and you were using Oracle you could use the REGEXP_COUNT() function directly.
If you want to continue using a Query transform then create a function in the database as a wrapper to the REGEXP_COUNT() function like this:
CREATE OR REPLACE FUNCTION USER_REGEXP_COUNT(STRING_TO_LOOK_IN VARCHAR2,
STRING_TO_LOOK_UP VARCHAR2)
RETURN NUMBER
IS
BEGIN
RETURN REGEXP_COUNT(STRING_TO_LOOK_IN, STRING_TO_LOOK_UP);
END;
/
Then import the function USER_REGEXP_COUNT into your Datastore. You can then use it in the Query transform.
@eganjp: This is exactly what I was looking for . It works the way I want and eased my complex imaginations for the logic. We have Oracle as the database, so I can directly use that REGEXP_COUNT() function in the SQL transform. Thanks for writing up the function too