Checking if not numeric value

[devidotcom]

Hi All,

I have a small query. I have a varchar field of length 40. Need to check if any character in that field is numeric. Is there a function to do this in a transformer stage.
....:(

Thanks

[devidotcom]

I have found a way to check if the field has a numeric character in it.

For example of Str='fare3d'
then we can use the count function
if count(Str,'3')>0 Then 1 Else 0
The above condition could be extended for all the numeric numbers 1,2,3,4,5,6,7,8,9,0

But is there another way out.

[chulett]

Have you investigated the IsValid() function?

(sorry, misread the requirement - never mind)

[devidotcom]

Yes Chulett I did. I tried using IsValid() function but in vain.
Correct me if I am wrong.

If I have a char value as Str="devi1" then this is a valid char value.
My requirement is to check if a char field has atleast one numeric value in it.
If I say IsValid(StringToDecimal(Str),"Decimal") this would not work as Str is not numeric value completely but one once char in it is '1'

[ray.wurlod]

There's no inbuilt function, and if you wrote your own it would need to check all ten as well. So the extended expression with ten Index() functions is about as good as you're going to get.

If the data volumes are small (say less than a couple of million rows) you might consider using a BASIC Transformer stage, which will give you access to the Oconv() function - you could then test

Code: Select all

InLink.TheString = Oconv(InLink.TheString, "MC/N")

[balajisr]

You can also convert all the numeric values to a empty string and compare the resultant string length with the orginal string length to find out if there are any numeric values.

Code: Select all

if Len(in.Col1) = Len(Convert('0123456789','', in.Col1)) then 0 else 1

The above code returns 1 if there are numeric values and 0 if there are no numeric values in column Col1.

[devidotcom]

Hi,
Thats an alternative and a shorter one thanks...