PLEASE TRY TO SOLVE THIS SCENARIO

Hi

I have the data like this
AB1CD3E4

I want to load without numbers

ABCDE

hOW CAN I DO THIS
CAN ANY BODY HELP IN THIS SCENARIO


martin_bantumilli (BOB member since 2011-09-27)

Use replace_substr function


Mayank R (BOB member since 2012-02-05)

What is the backend database?


jagpreet (BOB member since 2012-01-26)

custom function to do this



#Initalise
$i = 1;
$V_WORKING = '';

while ($i < length($INPUT_STRING))
begin
	#for each character find if the character is numeric
	if (match_regex( substr($INPUT_STRING, $i, 1), '[0-9]', NULL) = 0)
	begin
		#if so append it to the working string
		$V_WORKING = $V_WORKING || substr($INPUT_STRING, $i, 1);
	end
	#increment the loop counter
	$i = $i + 1;

end

#output the required string.
return $V_WORKING;

you just need to set up the variables.

cheers
Dave


davc4 :uk: (BOB member since 2009-07-03)

Word of caution on using match_regex!! make sure $INPUT_STRING is not null


BoDuDe :zimbabwe: (BOB member since 2005-12-19)

I realised that NULL could be an issue here but i have tested and everything works okay,

the while loop does not let the null loop values as 1 is > null,
also on the match regex supplying null to the function returns null != 0

to be on the safe side we probably should nvl the input string to be ‘’


davc4 :uk: (BOB member since 2009-07-03)

Hi I guess there are 2 small typos in the code: it takes all non numbers instead of the numbers and it doesn’t iterate to the end. This should do it:


$i = 1; 
$V_WORKING = ''; 

while ($i <= length($INPUT_STRING)) 
begin 
   #for each character find if the character is numeric 
   if (match_regex( substr($INPUT_STRING, $i, 1), '[0-9]', NULL) = 1) 
   begin 
      #if so append it to the working string 
      $V_WORKING = $V_WORKING || substr($INPUT_STRING, $i, 1); 
   end 
   #increment the loop counter 
   $i = $i + 1; 

end 

#output the required string. 
return $V_WORKING;

pbel (BOB member since 2012-08-22)

Hi,

Can you please tell me how to use a variable in the pattern of match_regex?
My aim is to use it in a function for checking the fields with different length
eg:
$V_LEN =length($DATA_VAL1) ;
$V_PATTERN = ‘9{[$V_LEN]}’;
print(’ $V_PATTERN:[ $V_PATTERN]’);

output = $V_PATTERN:9{3}

$v_status =match_regex($DATA_VAL1, ‘9{[$V_LEN]}’,null) ;

Thanks in Advance,


maryjohn (BOB member since 2012-08-22)

Another method would be to go with ASCII function. Slight modification in the code provided.

$i = 1;
$V_WORKING = ‘’;

while ($i <= length($INPUT_STRING))
begin
#for each character find if the character is numeric
if (ascii( substr($INPUT_STRING, $i, 1)) >=65)
begin
#if so append it to the working string
$V_WORKING = $V_WORKING || substr($INPUT_STRING, $i, 1);
end
#increment the loop counter
$i = $i + 1;

end

#output the required string.
return $V_WORKING;


tikkanipraveen :india: (BOB member since 2008-09-23)