Hi

I'm trying to create a COMPUTED field that does the same as my Delphi code.

What I do now is:-
--------------------------------------------------------------------------------------
function TfrmMain.CleanupPhoneNo(PhoneNo: string): string;
{ Replace non (0-9) with nuffink, allow leading '+' }
const
 sOKChar = '0123456789';
var
 i, j: Integer;
 s: string;
begin
 s := PhoneNo;
 if Copy(s, 1, 1) = '+' then
   j := 2
 else
   j := 1;
 for i := j to Length(s) do
   if Pos(UpperCase(s[i]), sOKChar) = 0 then
     s[i] := ' ';  // that's a single space character
 Result := StringReplace(s, ' ', '', [rfReplaceAll]);
end;
----------------------------------------------------------------------

e.g.  "+64 21-123 1234" comes out as "+64211231234"
       "021 123 123 (Fred's mobile)"  comes out a "021123123".

Rather than having to put this function into a number of different applications that all update the same table, I thought that having two fields PhoneNo and PhoneNoUsed would work.  PhoneNoUsed would be computed from PhoneNo.

However, I can't think how to do this in SQL.

Any help (including "Can't be done"!) appreciated.

Cheers

Jeff

Jeff Cook wrote:

Hi

>> I'm trying to create a COMPUTED field that does the same as my Delphi code.

Here is what I've done for now - a bit ugly as the field is VARCHAR(20) and the code would fail if I changed the size at a later date.

      IF (SUBSTRING(PhoneNo FROM 1 FOR 1) IN ('+','0','1','2','3','4','5','6','7','8','9') THEN
          SUBSTRING(PhoneNo FROM 1 FOR 1) ELSE '') +
      IF (SUBSTRING(PhoneNo FROM 2 FOR 1) IN ('0','1','2','3','4','5','6','7','8','9') THEN
          SUBSTRING(PhoneNo FROM 2 FOR 1) ELSE '')  +
      IF (SUBSTRING(PhoneNo FROM 3 FOR 1) IN ('0','1','2','3','4','5','6','7','8','9') THEN
          SUBSTRING(PhoneNo FROM 3 FOR 1) ELSE '') +
      IF (SUBSTRING(PhoneNo FROM 4 FOR 1) IN ('0','1','2','3','4','5','6','7','8','9') THEN
          SUBSTRING(PhoneNo FROM 4 FOR 1) ELSE '') +
      IF (SUBSTRING(PhoneNo FROM 5 FOR 1) IN ('0','1','2','3','4','5','6','7','8','9') THEN
          SUBSTRING(PhoneNo FROM 5 FOR 1) ELSE '') +
      IF (SUBSTRING(PhoneNo FROM 6 FOR 1) IN ('0','1','2','3','4','5','6','7','8','9') THEN
          SUBSTRING(PhoneNo FROM 6 FOR 1) ELSE '') +
      IF (SUBSTRING(PhoneNo FROM 7 FOR 1) IN ('0','1','2','3','4','5','6','7','8','9') THEN
          SUBSTRING(PhoneNo FROM 7 FOR 1) ELSE '') +
      IF (SUBSTRING(PhoneNo FROM 8 FOR 1) IN ('0','1','2','3','4','5','6','7','8','9') THEN
          SUBSTRING(PhoneNo FROM 8 FOR 1) ELSE '') +
      IF (SUBSTRING(PhoneNo FROM 9 FOR 1) IN ('0','1','2','3','4','5','6','7','8','9') THEN
          SUBSTRING(PhoneNo FROM 9 FOR 1) ELSE '') +
      IF (SUBSTRING(PhoneNo FROM 10 FOR 1) IN ('0','1','2','3','4','5','6','7','8','9') THEN
          SUBSTRING(PhoneNo FROM 10 FOR 1) ELSE '') +
      IF (SUBSTRING(PhoneNo FROM 11 FOR 1) IN ('0','1','2','3','4','5','6','7','8','9') THEN
          SUBSTRING(PhoneNo FROM 11 FOR 1) ELSE '') +
      IF (SUBSTRING(PhoneNo FROM 12 FOR 1) IN ('0','1','2','3','4','5','6','7','8','9') THEN
          SUBSTRING(PhoneNo FROM 12 FOR 1) ELSE '')  +
      IF (SUBSTRING(PhoneNo FROM 13 FOR 1) IN ('0','1','2','3','4','5','6','7','8','9') THEN
          SUBSTRING(PhoneNo FROM 13 FOR 1) ELSE '') +
      IF (SUBSTRING(PhoneNo FROM 14 FOR 1) IN ('0','1','2','3','4','5','6','7','8','9') THEN
          SUBSTRING(PhoneNo FROM 14 FOR 1) ELSE '') +
      IF (SUBSTRING(PhoneNo FROM 15 FOR 1) IN ('0','1','2','3','4','5','6','7','8','9') THEN
          SUBSTRING(PhoneNo FROM 15 FOR 1) ELSE '') +
      IF (SUBSTRING(PhoneNo FROM 16 FOR 1) IN ('0','1','2','3','4','5','6','7','8','9') THEN
          SUBSTRING(PhoneNo FROM 16 FOR 1) ELSE '') +
      IF (SUBSTRING(PhoneNo FROM 17 FOR 1) IN ('0','1','2','3','4','5','6','7','8','9') THEN
          SUBSTRING(PhoneNo FROM 17 FOR 1) ELSE '') +
      IF (SUBSTRING(PhoneNo FROM 18 FOR 1) IN ('0','1','2','3','4','5','6','7','8','9') THEN
          SUBSTRING(PhoneNo FROM 18 FOR 1) ELSE '') +
      IF (SUBSTRING(PhoneNo FROM 19 FOR 1) IN ('0','1','2','3','4','5','6','7','8','9') THEN
          SUBSTRING(PhoneNo FROM 19 FOR 1) ELSE '') +
      IF (SUBSTRING(PhoneNo FROM 20 FOR 1) IN ('0','1','2','3','4','5','6','7','8','9') THEN
          SUBSTRING(PhoneNo FROM 20 FOR 1) ELSE '')

Cheers

Jeff

Jeff

Apart from the fact that you could replace IN ('+','0','1','2','3','4','5','6','7','8','9') with IN ('+0123456789') I don't think there's much you can do. You can't call functions for either COMPUTED or GENERATED columns and that essentially restricts you to a single line of SQL which must have an output that is compatible with the specification for the column.

What I do is I have a trigger on the phone number columns which calls a function to format the number depending on the STD code eg:

01453 731 222
0118 922 8200
020 7468 8000
+39 069 454 998 1
07 985 033 916

When I want to compare I do

REPLACE(' ','',_Switchboard) = REPLACE(' ','',entered)

in my query generator

This means that I have an eyeballs friendly version which is consistently formatted, and because its consistently formatted a simple get rid of spaces allows an easy compare. I do transform the entered data at the point of entry so its compatible.


Roy Lambert

Jeff,

how about using a function like this:

SCRIPT

BEGIN
EXECUTE IMMEDIATE 'CREATE FUNCTION "GetCleanNumber" (IN "PhoneNumber"
VARCHAR(20) COLLATE "DEU_CI")
RETURNS VARCHAR(20) COLLATE "DEU_CI"
BEGIN

DECLARE Temp VARCHAR;

SET Temp = REPLACE( ''-'' WITH '''' IN PhoneNumber);
SET Temp = REPLACE( '' '' WITH '''' IN Temp);
RETURN Temp;

END
VERSION 1.00';

END

Uli

Uli



Two small points

1. Its more a matter of removing any non-numeric character (where + is and - isn't) and
2.

[Window Title]
Error

[Content]
The following error occurred with the execution of the SQL for the last operation:

ElevateDB Error #700 An error was found in the statement at line 2 and column 75 (Invalid expression GetCleanNumber found, user-defined function references not allowed)

Would you like to copy the SQL to a new SQL editor window so that you can correct the problem and attempt to execute the SQL again ?

[Yes] [No]


If this, or scripts were allowed it would be brilliant.

Roy Lambert

Roy,

> 1. Its more a matter of removing any non-numeric character (where + is and - isn't) and

True, but I assume that nothing but '+', '-' and ' ' should be allowed
in a phonenumber field.

> 2.
> ElevateDB Error #700 An error was found in the statement at line 2 and column 75 (Invalid expression GetCleanNumber found, user-defined function references not allowed)

Also true. But using such a function would work either in a query or a
trigger to populate another field.

Anyway I agree that using functions in computed fields would be great.

Uli

Uli


>True, but I assume that nothing but '+', '-' and ' ' should be allowed
>in a phonenumber field.

Its a wonderful theory

>Also true. But using such a function would work either in a query or a
>trigger to populate another field.

It its just for use in a query then a nice function would be ideal. I do have computed & generated columns in use in tables as well - they're just like calculated fields.


>Anyway I agree that using functions in computed fields would be great.

Yeah he says drooling.

Roy

Roy,

<< You can't call functions for either COMPUTED or GENERATED columns and that essentially restricts you to a single line of SQL which must have an output that is compatible with the specification for the column. >>

Not true.  You can call functions in GENERATED columns.  It's just COMPUTED columns that you can't do so with, because they are re-computed on the client, which doesn't have access to the catalog, etc., which are necessary for resolving function references.

Tim Young
Elevate Software
www.elevatesoft.com

Tim


>Not true. You can call functions in GENERATED columns. It's just COMPUTED columns that you can't do so with, because they are re-computed on the client, which doesn't have access to the catalog, etc., which are necessary for resolving function references.

I'm sure you've said that before somewhere but I'd obviously forgotten. Would it be possible to enhance COMPUTED to use simple scripts. I know its not allowed to do a SELECT or use UDFs that would increase its usefulness enormously.

From a personal viewpoint I'm not keen on GENERATED columns. Since it stores the data I prefer to use a real column and a trigger or do the transform during data entry. I'm sure lots will disagree

Roy Lambert

On 24/07/2015 7:30 p.m., Roy Lambert wrote:
> Jeff
>
> Apart from the fact that you could replace IN ('+','0','1','2','3','4','5','6','7','8','9') with IN ('+0123456789') I don't think there's much you can do. You can't call functions for either COMPUTED or GENERATED columns and that essentially restricts you to a single line of SQL which must have an output that is compatible with the specification for the column.
>

Hi Uli, Roy and Tim

Apologies for slow response - weekend in NZ and I took it off!

Looks like, my solution is close to the best for the moment. I'll take
Roy's suggestion to make it prettier.

I haven't dipped my toe into Functions and generated columns yet ...
coming for DBISAM3 to EDB by the slow route.  (Hums some Scottish melody
about High Roads and Low Roads ...)

Cheers

Jeff