Dataphor - trimming digits and letters from a string

Trimming leading zeros and trailing letters from a string.

This question was raised in the thread:
microsoft.public.sqlserver.programming
Tuesday, June 05, 2007 2:08 PM
Couple of update statements
http://tinyurl.com/2n73xf

This can done quite easily in Dataphors D4 language without the
need of combining string functions into complicated expressions.

The following D4 constructs can be used to return a string
meeting the criteria:

Indexer Expression
http://www.alphora.com/docs/O-System.iIndexer.html

IndexOfAny
http://www.alphora.com/docs/O-System.IndexOfAny.html

Concat (Concatenate)
http://www.alphora.com/docs/O-System.Concat.html

For updating using a table variable or view see:

Join/Outer Join/Lookup
http://www.alphora.com/docs/DDGRepresentingDatawithTablesandViews-DerivedTableVariables-Join.html
http://www.alphora.com/docs/D4LGTableExpressions-OuterJoin.html

Here is some sample data entered into MS Sql Server 2005 that
we will access with Dataphor:

create table LTLData (RowID int primary key,ProNum varchar(25) )
insert into LTLData  Values (1,'00234')
insert into LTLData  Values (2,'030234BD')
insert into LTLData  Values (3,'33030234BD')
insert into LTLData  Values (4,'0233030234BD')
insert into LTLData  Values (5,'0000234BD')
insert into LTLData  Values (6,'233030234')
insert into LTLData  Values (7,'00000ABD')
insert into LTLData  Values (8,'BDBDBD')

So for example given the string ''030234BD' we want to trim the
leading '0' and the trailing 'BD' and return '30234'.

An easy way to do this is transpose the string into rows of a
single character and work with the rows to omit the leading
zeros and trailing letters. Once the offending rows (characters)
are omitted then concatenate the rows to form the new string.

Here we use a table of numbers (numbers with a single column num)
and the indexer to split each string into rows of a single character.
A list of digits of type string (NumList1) is used with IndexOfAny
to add the first occurrance of any non zero digit in the string to
each row (FirstDigit). If a string has no digit from 1-9 then IndexOfAny
returns -1. The 'with {IgnoreUnsupported = 'true'}' is used to suppress
the message Dataphor returns that says there is no support in Sql Server
for the IndexOfAny function.

var NumList1:list(String):={'1','2','3','4','5','6','7','8','9'};
select
(
 numbers //table of digits (num) from 0-100.
   times //Like an sql cross join
         //Get the count (Cnt) of the items in the string and location of 1st digit.
     LTLData add{Length(ProNum.Trim()) Cnt,ProNum.IndexOfAny(NumList1) FirstDigit}
       with {IgnoreUnsupported = 'true'}
        where num<=Cnt-1
          {RowID,ProNum,num Index,FirstDigit,ProNum[num] Char}
 )
  order by {RowID,Index};

Here is a sample of the result:

RowID ProNum       Index FirstDigit Char
----- ------------ ----- ---------- ----
1     00234        0     2          0   
1     00234        1     2          0   
1     00234        2     2          2   
1     00234        3     2          3   
1     00234        4     2          4   
2     030234BD     0     1          0   
2     030234BD     1     1          3   
2     030234BD     2     1          0   
2     030234BD     3     1          2   
2     030234BD     4     1          3   
2     030234BD     5     1          4   
2     030234BD     6     1          B   
2     030234BD     7     1          D   
.

7     00000ABD     0     -1         0   
7     00000ABD     1     -1         0   
7     00000ABD     2     -1         0   
7     00000ABD     3     -1         0   
7     00000ABD     4     -1         0   
7     00000ABD     5     -1         A   
7     00000ABD     6     -1         B   
7     00000ABD     7     -1         D   

We now add a single where statement to omit leading zeros and
trailing letters.

var NumList1:list(String):={'1','2','3','4','5','6','7','8','9'};
select
(
 numbers //table of digits (num) from 0-100.
   times //Like a cross join
       //Get the count (Cnt) of the items in the string and location of 1st digit.
     LTLData add{Length(ProNum.Trim()) Cnt,ProNum.IndexOfAny(NumList1) FirstDigit}
       with {IgnoreUnsupported = 'true'}
        where num<=Cnt-1
          {RowID,ProNum,num Index,FirstDigit,ProNum[num] Char}
 )
       where (Index>=FirstDigit) and (FirstDigit<>-1 ) and (IsDigit(Char))
   order by {RowID,Index};

RowID ProNum       Index FirstDigit Char
----- ------------ ----- ---------- ----
1     00234        2     2          2   
1     00234        3     2          3   
1     00234        4     2          4   
2     030234BD     1     1          3   
2     030234BD     2     1          0   
2     030234BD     3     1          2   
2     030234BD     4     1          3   
2     030234BD     5     1          4   
3     33030234BD   0     0          3   
3     33030234BD   1     0          3   
3     33030234BD   2     0          0   
3     33030234BD   3     0          3   
3     33030234BD   4     0          0   
3     33030234BD   5     0          2   
3     33030234BD   6     0          3   
.
6     233030234    0     0          2   
6     233030234    1     0          3   
6     233030234    2     0          3   
6     233030234    3     0          0   
6     233030234    4     0          3   
6     233030234    5     0          0   
6     233030234    6     0          2   
6     233030234    7     0          3   
6     233030234    8     0          4   

The where statement did not affect RowID 6 but completely eliminated
RowIDs 7 and 8.

The Concat operator can now be used to form a new string based on the
rows meeting the criteria. The Index column is used in Concat to order
the new string in the same way it was transposed. A delimiter (Del) is
defined as an empty string ('') so when the new string is formed there
are is no separation between the characters (Char).

Instead of operating on the rows of LTLData, here is a select statement
that trims a single string. The 'with {IgnoreUnsupported = 'true'}' is
used with Concat since we have not mapped Concat to any Sql Server
function. Like IndexOfAny the Dataphor server will assume responsibility
for this operation. (Interested readers can search Dataphor help for
'query chunking' to see how Dataphor interacts with a device, ie Sql Server.)

var NumList1 := {'1','2','3','4','5','6','7','8','9'};
var Str:='030234BD';
select
Concat({Char,Del}
from
 (
  numbers
   where num<=Length(Str.Trim())-1
    {Str StrNum,num Index,Str[num] Char,Str.IndexOfAny(NumList1) FirstDigit,'' Del}
      with {IgnoreUnsupported = 'true'}
        where (Index>=FirstDigit) and (FirstDigit<>-1 ) and (IsDigit(Char))
  )
    order by {Index}  ) with {IgnoreUnsupported = 'true'};
 
Which returns '30234'.

We can now use the above select to create an operator that will return
a new string for each row of LTLData.

create operator TrimStr(Str:String):String
begin
var NumList1 := {'1','2','3','4','5','6','7','8','9'};
result:= 
Concat({Char,Del}
from
 (
  numbers
   where num<=Length(Str.Trim())-1
    {Str StrNum,num Index,Str[num] Char,Str.IndexOfAny(NumList1) FirstDigit,'' Del}
      with {IgnoreUnsupported = 'true'}
        where (Index>=FirstDigit) and (FirstDigit<>-1 ) and (IsDigit(Char))
  )
    order by {Index}  ) with {IgnoreUnsupported = 'true'};
end;   

We now use operator TrimStr to return the correctly trimmed new string (NewStr).

select
 LTLData add{TrimStr(ProNum) NewStr}
          with {IgnoreUnsupported = 'true'};
         
RowID ProNum       NewStr    
----- ------------ ----------
1     00234        234       
2     030234BD     30234     
3     33030234BD   33030234  
4     0233030234BD 233030234 
5     0000234BD    234       
6     233030234    233030234 
7     00000ABD     <No Value>
8     BDBDBD       <No Value>           

The ProNum column in LTLData can now be updated using TrimStr.
The 'IsNotNil()' is similar to the sql 'not IsNull()'. So only ProNum
values will be updated where TrimStr return a non-nil(null) value.
(Note that D4 uses the term 'nil' whereas sql uses 'null'.) The
"with {ShouldSupport='false'}" tag is used to tell the Dataphor
sever to take responsibility for evaluating 'IsNotNil(TrimStr(ProNum))'.
There is no way for Sql Server to evaluate this expression. A
"{ShouldSupport='true'}" (the default) means Sql Server should
try to support the expression. Without the "with {ShouldSupport='false'}"
the update will execute but Dataphor will issue a warning message.

update LTLData
  set { ProNum:= TrimStr(ProNum) }
    where IsNotNil(TrimStr(ProNum) with {ShouldSupport='false'} )  ;     

select LTLData;  

RowID ProNum   
----- ---------
1     234      
2     30234    
3     33030234 
4     233030234
5     234      
6     233030234
7     00000ABD 
8     BDBDBD 


Finally, we recreate LTLData and show a query which uses Concat in
a group by to form new trimmed strings for each RowID.

var NumList1 := {'1','2','3','4','5','6','7','8','9'};
select
 numbers
   times
    LTLData add{Length(ProNum.Trim()) Cnt,ProNum.IndexOfAny(NumList1) FirstDigit}
        with {IgnoreUnsupported = 'true'}
     where num<=Cnt-1
      {RowID,ProNum,num Index,FirstDigit,ProNum[num] Char,'' Del}
        where (Index>=FirstDigit) and (FirstDigit<>-1 ) and (IsDigit(Char))
            group by {RowID}
              add
                 {Max(ProNum) ProNum, Concat(Char,Del order by {RowID,Index}) NewPro}
                    order by {RowID};

RowID ProNum       NewPro   
----- ------------ ---------
1     00234        234      
2     030234BD     30234    
3     33030234BD   33030234 
4     0233030234BD 233030234
5     0000234BD    234      
6     233030234    233030234


Update with a view.
We can define a view and use it in a join to update ProNum with NewPro (from the view).

create view LTLDataView                    
 numbers
   times
    LTLData add{Length(ProNum.Trim()) Cnt,ProNum.IndexOfAny( {'1','2','3','4','5','6','7','8','9'}) FirstDigit}
        with {IgnoreUnsupported = 'true'}
     where num<=Cnt-1
      {RowID,ProNum,num Index,FirstDigit,ProNum[num] Char,'' Del}
        where (Index>=FirstDigit) and (FirstDigit<>-1 ) and (IsDigit(Char))
            group by {RowID}
              add
                 {Max(ProNum) ProNum, Concat(Char,Del order by {RowID,Index}) NewPro} ;
                
The lookup operator is similar to a join but can be used to
control just which table(s) should be the target of an update.
We only wish to update LTLData and don't want to go thru the
view to do it. We only want the view to supply the new string NewPro.
The lookup encapsulates this idea. Note the lookup (join) is a
natural join using RowID.

update LTLData lookup (LTLDataView remove{ProNum} )
   set { ProNum:= NewPro } ;  

select LTLData;

RowID ProNum   
----- ---------
1     234      
2     30234    
3     33030234 
4     233030234
5     234      
6     233030234
7     00000ABD 
8     BDBDBD   

Instead of a view a table variable could be also be used.

var NumList1 := {'1','2','3','4','5','6','7','8','9'};
var Y:=
 numbers
   times
    LTLData add{Length(ProNum.Trim()) Cnt,ProNum.IndexOfAny(NumList1) FirstDigit}
        with {IgnoreUnsupported = 'true'}
     where num<=Cnt-1
      {RowID,ProNum,num Index,FirstDigit,ProNum[num] Char,'' Del}
        where (Index>=FirstDigit) and (FirstDigit<>-1 ) and (IsDigit(Char))
            group by {RowID}
              add
                 {Max(ProNum) ProNum, Concat(Char,Del order by {RowID,Index}) NewPro} ;

update LTLData lookup (Y remove{ProNum} )
   set { ProNum:= NewPro } ;  

select LTLData;

RowID ProNum   
----- ---------
1     234      
2     30234    
3     33030234 
4     233030234
5     234      
6     233030234
7     00000ABD 
8     BDBDBD   

Bye for now,
steve