Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 3 of 3 total
Thread Feature request or a simplest way
Tue, Oct 20 2020 1:55 PMPermanent Link

Jose Eduardo Helminsky

HPro Informatica

Hi everyone

I have a string field with numeric data with other charactes. I need to get only numbers from this field and I am using the following SQL statement to do that. So far, so good, this is working. My question: Is there another way to do this ? Is there a way to request to ElevateSoft to add some function called "OnlyNumber" to do that ?

select tel,
if(pos(substring(tel, 1,1) in '1234567890'),substring(tel, 1,1),'')+
if(pos(substring(tel, 2,1) in '1234567890'),substring(tel, 2,1),'')+
if(pos(substring(tel, 3,1) in '1234567890'),substring(tel, 3,1),'')+
if(pos(substring(tel, 4,1) in '1234567890'),substring(tel, 4,1),'')+
if(pos(substring(tel, 5,1) in '1234567890'),substring(tel, 5,1),'')+
if(pos(substring(tel, 6,1) in '1234567890'),substring(tel, 6,1),'')+
if(pos(substring(tel, 7,1) in '1234567890'),substring(tel, 7,1),'')+
if(pos(substring(tel, 8,1) in '1234567890'),substring(tel, 8,1),'')+
if(pos(substring(tel, 9,1) in '1234567890'),substring(tel, 9,1),'')+
if(pos(substring(tel,10,1) in '1234567890'),substring(tel,10,1),'')+
if(pos(substring(tel,11,1) in '1234567890'),substring(tel,11,1),'')+
if(pos(substring(tel,12,1) in '1234567890'),substring(tel,12,1),'')+
if(pos(substring(tel,13,1) in '1234567890'),substring(tel,13,1),'')+
if(pos(substring(tel,14,1) in '1234567890'),substring(tel,14,1),'')+
if(pos(substring(tel,15,1) in '1234567890'),substring(tel,15,1),'')+
if(pos(substring(tel,16,1) in '1234567890'),substring(tel,16,1),'')+
if(pos(substring(tel,17,1) in '1234567890'),substring(tel,17,1),'')+
if(pos(substring(tel,18,1) in '1234567890'),substring(tel,18,1),'')+
if(pos(substring(tel,19,1) in '1234567890'),substring(tel,19,1),'')+
if(pos(substring(tel,20,1) in '1234567890'),substring(tel,20,1),'')
from hemp

Thanks in advance.
Any tips will help me.

Regards
Eduardo
Tue, Oct 20 2020 2:14 PMPermanent Link

Jose Eduardo Helminsky

HPro Informatica

After thinking better I have found a simplest solution:

CREATE FUNCTION "ONLYNUMBER" (IN "cTxt" VARCHAR COLLATE "ANSI_CI")
RETURNS VARCHAR
BEGIN
    declare cAux varchar default '';
    declare nI integer;
    declare nM integer;
    set nI = 1;
    set nM = Length(cTxt);
    while nI <= nM do
       set cAux = cAux + if(pos(substring(cTxt,nI,1) in '1234567890'),substring(cTxt,nI,1),'');
       set nI = nI + 1;
    end while;
    return cAux;
END;

Then I just need to use:
select onlynumber(tel) from hemp
Wed, Oct 21 2020 1:07 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Jose


That's pretty much what I would have recommended. Don't forget as you go forward that you can create a dll which contains callable Delphi functions.

My personal favourite doesn't make it to a function but is called

else if ckn = 'soundex' then begin
 if ParamValues.FindByName('sLength').Null
  then ParamValues.FindByName('Result').AsString := Soundex(ParamValues.FindByName('FldStr').AsString, 4)
 else ParamValues.FindByName('Result').AsString := Soundex(ParamValues.FindByName('FldStr').AsString, ParamValues.FindByName('sLength').AsInteger)


Roy Lambert
Image