Count the number of occurence of numbers in a string

dataservices job design
1

Hello All,

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:

Record no,Text,v1.1,v1.2.5,v1.3…
1, asd 1.1-adklfha 1.1-asdklfh 1.2.5 ajdlfn, 2,1,0
2, asllj 1.2.5 adlkal 1.3 lknln, 0, 1,1
3, aslk 1.3 alkfja 1.1 asilj 1.2.5 ljl 1.2.5 kjh 1.2.5 adf, 1,3,1

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.

Thanks in advance.

Thanks
Ramesh

rookie86 :india: (BOB member since 2009-09-11)

2

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. :frowning: [/edit]

jlynn73 :us: (BOB member since 2009-10-27)

3

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 :us: (BOB member since 2007-09-12)

4

Thanks for your valuable replies.

@eganjp: This is exactly what I was looking for :+1: . 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 :smiley:

Thanks
Ramesh

rookie86 :india: (BOB member since 2009-09-11)

5

Not running on the idea.

Tweaking the function/grabbing the idea from the below post might help!!!

ganeshxp :us: (BOB member since 2008-07-17)