how to allow only interger in coulumn by using fuctions

[Former Member]

Hi Experts,

we have the data coulumns like Adress1, Adress 2, Adress 3, Phone number, country .....etc

in this cluomns the data is mixing like ... in adress there is phone number & in phone number there is Adress , & in country intigers ..

so now i need to filter these all coulumns in certain strucutre according to coulumn names.

here i need to restrict the data in country i no need to allow intigers & in phone number i need to allow only intigers.

how to do this in BODS

is it possible this by using functions in query transform .. please help me to solve this.

[Former Member]

Hi ,

You can use the function is_valid_int to check whether your input field is an integer or not.

The output of this function is  1 incase it is valid and 0 incase not valid

For example lets say your phone number is '123456' . So in your query transform add 2 new columns, is_phone_number and is_address.

In the mapping tab, write the logic is_valid_int(phone_number, '#################################')

Similiary for the address write is_valid_int(address, '##################################')

Note : Put a large number of #.

If your output is 1 , then it contains all integers, if 0 then all characters. You can then filter your records based on 1 and 0.

Please refer to the below screenshot.

Hope this helps.

Cheers

Annesha

[Former Member]

Hi Annesha,

Thank u for responce .....But i no need to check whether it is "int" or not....please find this exapmle

we have the data like

Address1     Address2     country          Pincode     PH-num

2 nd floor     esr          517555           999999999     India

menta          24569    888888887       <blank>        navel

hitech          2-457     98563214          india            56987

2nd road     <blank>    india               52369         9632581111

so we need the arranege this data accourding to coulmn names

& apply data types PH-num=int, Address=Varcher, Country= 'char'.

how can it is possible in BODS.

Thanks & Regards,

Mahe

[Former Member]

Hi Mahe,

I got your question now. You need to arrange the data cloumns and place them under their proper header, right .

For example

Address1 - 2nd Floor

Address2-esr

country- India

pincode- 517555 and

phone number - 9999999999.

For this you need to write a custom function. Pass all the columns in the custom function and store them in local variables. Then first check which local variable has the country name ( by matching with a countries database table ) . Once you find the match assign that local variable to the return parameter of country. Now from 5 local variables, find which is number and contains only 6 digits and that will be pincode , similarly for phone , find which contains 10 digits, and then assign it to the return parameter of pincode and phone respectively.The last two left will be address1 and address 2.

Hope this works.

Thanks

Annesha

[Former Member]

Hi Annesha,

Thank u for helpful answer.

if i am correct it can be done by data profiling also ... if u know this method please share this method also data profiling ( i think using data cleanse & address cleanse transforms )

Thanks & Regards,

Mahe