Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 8 of 8 total |
Reducing blanks in a field... |
Fri, Apr 18 2008 2:33 PM | Permanent Link |
Uli Becker | I store account statements from a bank in a table and extract information from this.
In many of these statements there are blanks in the text, sometimes more than 10 in a row. The number of blanks is different in each record. I would like to reduce alle blanks to only one space. Of course I can use a loop like this: update kontoauszug set Zweck = REPLACE( ' ' WITH ' ' IN Zweck); update kontoauszug set Zweck = REPLACE( ' ' WITH ' ' IN Zweck); etc. But is there a more effective way to do it? Thanks. Uli |
Fri, Apr 18 2008 2:52 PM | Permanent Link |
Fernando Dias Team Elevate | Uli,
1. You can loop only the first statement, until there are no updated rows: update kontoauszug set Zweck = REPLACE( ' ' WITH ' ' IN Zweck); 2. If you know the maximum number of possible blanks (and if it's a small number), you can execute the sequence of statements you wrote, but I think they will execute fast in reverse order, at least for some cases. For example: update kontoauszug set Zweck = REPLACE( ' ' WITH ' ' IN Zweck); update kontoauszug set Zweck = REPLACE( ' ' WITH ' ' IN Zweck); update kontoauszug set Zweck = REPLACE( ' ' WITH ' ' IN Zweck); -- Fernando Dias [Team Elevate] |
Fri, Apr 18 2008 3:13 PM | Permanent Link |
"Uli Becker" | Fernando,
> You can loop only the first statement, until there are no updated rows: > update kontoauszug set Zweck = REPLACE( ' ' WITH ' ' IN Zweck); Ok, that's clear. > If you know the maximum number of possible blanks (and if it's a small > number), you can execute the sequence of statements you wrote, but I think > they will execute fast in reverse order, at least for some cases. I see. The question is if that's really faster. In either way it seems that all rows have to be scanned. Thanks. Uli |
Fri, Apr 18 2008 6:40 PM | Permanent Link |
Fernando Dias Team Elevate | Uli
> I see. The question is if that's really faster. In either way it seems > that all rows have to be scanned. Yes, I agree. A better solution would be to write a function to remove the blanks. -- Fernando Dias [Team Elevate] |
Sat, Apr 19 2008 3:56 AM | Permanent Link |
Uli Becker | Fernando,
> A better solution would be to write a function to remove the blanks. Could you explain that further? Regards Uli |
Sat, Apr 19 2008 7:00 AM | Permanent Link |
Fernando Dias Team Elevate | Uli
> Could you explain that further? First, you must create a function that removes double blanks from strings: --------------------------------- CREATE FUNCTION "RemoveBlanks" (IN "st" VARCHAR(512) COLLATE ANSI) RETURNS VARCHAR(512) COLLATE ANSI BEGIN Declare s Varchar(512); Set s = st; While Position(' ' In s) > 0 Do Set s = Replace(' ',' ',s); End While; Return s; END --------------------------------- You may also define an external Delphi function, instead; I don't know if that would execute faster; I never tested the relative performance of PSM functions vs. external functions. Then you just use the function in an sql update statement: ---------------------------------- update kontoauszug set Zweck = RemoveBlanks(Zweck); ---------------------------------- -- Fernando Dias [Team Elevate] |
Sat, Apr 19 2008 9:39 AM | Permanent Link |
Uli Becker | Fernando,
> CREATE FUNCTION "RemoveBlanks" (IN "st" VARCHAR(512) COLLATE ANSI) > RETURNS VARCHAR(512) COLLATE ANSI > BEGIN > Declare s Varchar(512); > Set s = st; > While Position(' ' In s) > 0 Do > Set s = Replace(' ',' ',s); > End While; > Return s; > END That's really good idea. Thank you! Regards Uli |
Mon, Apr 21 2008 9:34 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Fernando,
<< First, you must create a function that removes double blanks from strings: >> Nice job. One addition - you can just set the IN parameter and RETURNS as VARCHAR and don't have to specify a length. This is especially useful for functions whereby you have no idea how long the incoming string will be, nor do you know how long the resultant string will be. -- Tim Young Elevate Software www.elevatesoft.com |
This web page was last updated on Sunday, May 19, 2024 at 08:46 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |