Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 3 of 3 total |
Feature request or a simplest way |
Tue, Oct 20 2020 1:55 PM | Permanent 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 PM | Permanent 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 AM | Permanent Link |
Roy Lambert NLH Associates 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 |
This web page was last updated on Saturday, April 27, 2024 at 08:52 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |