cat > script <<'+-+-+-+-+-END-OF-FILE-+-+-+-+-+'
set MSQLSELWARN to "warn";
set MSQLECHO to "batch conv";
create emt (dat1 date(0), i1 integer, dec1 decimal(14,3), flt1 float);
insert emt set dat1 to "01/01/87";
select strdel("john",2,1) from emt;
select strdel("john",2,0) from emt;
select strdel("john",2,2) from emt;
select substr("john",2,2) from emt;
select lscan("john","hn") from emt;
select lscan("Johnson","n",2) from emt;
select strins("Johnson",1,"Jacob ") from emt;
select strins("Johson",lscan("Johson","s"),"n") from emt;
select tolower("UPPTOLOW") from emt;
select toupper("lowtoupp") from emt;
select toupper(substr("capitalize",1,1)) concat substr("capitalize",2) from emt;
select lpad(name," "),  lpad(name,"x"), lpad(name,'yz') from loans;
select lpad(name,"x",3), lpad(name,'.'), lpad(name,".",3) from loans;
select rpad(name," "),  rpad(name,"x"), rpad(name,'yz') from loans;
select rpad(name,"x",3), rpad(name,'.'), rpad(name,".",3) from loans;
select ltrim("raebggleave","garbage") from emt;
select rtrim("raebggleaveegabrag","garbage") from emt;
select rtrim("Horatio     ") concat " " concat ltrim("    Hornblower") from emt;
select strdel(name, lscan(name, 'on'), 3) from loans where 
  lscan(name, 'on') != 0;
update loans set name = toupper(name) where
  name = ltrim(rtrim(name, substr('qrst', 3)), substr('LMNO', 2));
select name from loans;
update loans set name  strins(name, rscan(tolower(name), 'on'),
  lpad(rpad(':', '<-', 2), '->', 2)) where
  rscan(tolower(name), 'on') != 0;
select name from loans;
select rscan("abcdab","ab",2) from emt;
drop emt;
create bt (b1 bulk(10,5,5), b2 bulk(8,4,4));
insert into bt set to '010203040506', '1122330055';
insert into bt set to 'ffee00ddffee', 'ffee';
select b1, substr(b1, 3), substr(b1,3,2) from bt;
select b1,b2, b2 concat b1 from bt;
select lscan(b1,b2), rscan(b1,b2) from bt;
select lscan(b1,b2,2), rscan(b1,b2,2) from bt;
select lscan(b1,b2,3), rscan(b1,b2,3) from bt;
select lscan(b1 concat b2,b2), rscan(b1 concat b2,b2) from bt;
select strdel(b1,3,2), b1, strdel(b2,2,3), b2 from bt; 
drop bt;
+-+-+-+-+-END-OF-FILE-+-+-+-+-+
chmod 644 script
cat > stdout <<'+-+-+-+-+-END-OF-FILE-+-+-+-+-+'
* * * create emt (dat1 date(0), i1 integer, dec1 decimal(14,3), flt1 float);
* insert emt set dat1 to "01/01/87";
* select strdel("john",2,1) from emt;
STRDEL( 'john', 2, 1)

jhn
* select strdel("john",2,0) from emt;
STRDEL( 'john', 2, 0)

john
* select strdel("john",2,2) from emt;
STRDEL( 'john', 2, 2)

jn
* select substr("john",2,2) from emt;
SUBSTR( 'john', 2, 2)

oh
* select lscan("john","hn") from emt;
LSCAN( 'john',  'hn')

                    3
* select lscan("Johnson","n",2) from emt;
LSCAN( 'Johnson',  'n', 2)

                         7
* select strins("Johnson",1,"Jacob ") from emt;
STRINS( 'Johnson', 1,  'Jacob ')

Jacob Johnson
* select strins("Johson",lscan("Johson","s"),"n") from emt;
STRINS( 'Johson', LSCAN( 'Johson',  's'),  'n')

Johnson
* select tolower("UPPTOLOW") from emt;
TOLOWER( 'UPPTOLOW')

upptolow
* select toupper("lowtoupp") from emt;
TOUPPER( 'lowtoupp')

LOWTOUPP
* select toupper(substr("capitalize",1,1)) concat substr("capitalize",2) from emt;
(TOUPPER(SUBSTR( 'capitalize', 1, 1)) concat SUBSTR( 'capitalize', 2))

Capitalize
* select lpad(name," "),  lpad(name,"x"), lpad(name,'yz') from loans;
LPAD(name,  ' ')      LPAD(name,  'x')      LPAD(name,  'yz')

                      xxxxxxxxxxxxxxxxxxxx  yzyzyzyzyzyzyzyzyzyz
Mosca                 Mosca                 Mosca
                      xxxxxxxxxxxxxxxxxxxx  yzyzyzyzyzyzyzyzyzyz
Jones                 Jones                 Jones
                   K  xxxxxxxxxxxxxxxxxxxK  yzyzyzyzyzyzyzyzyzKi
ilroy                 ilroy                 lroy
                Wlad  xxxxxxxxxxxxxxxxWlad  yzyzyzyzyzyzyzyzWlad
islaw                 islaw                 islaw
                      xxxxxxxxxxxxxxxxxxxx  yzyzyzyzyzyzyzyzyzyz
Jones                 Jones                 Jones
                      xxxxxxxxxxxxxxxxxxxx  yzyzyzyzyzyzyzyzyzyz
Mosca                 Mosca                 Mosca
                Wlad  xxxxxxxxxxxxxxxxWlad  yzyzyzyzyzyzyzyzWlad
islaw                 islaw                 islaw
                 Pet  xxxxxxxxxxxxxxxxxPet  yzyzyzyzyzyzyzyzPete
erson                 erson                 rson
                Wlad  xxxxxxxxxxxxxxxxWlad  yzyzyzyzyzyzyzyzWlad
islaw                 islaw                 islaw
                      xxxxxxxxxxxxxxxxxxxx  yzyzyzyzyzyzyzyzyzyz
Jones                 Jones                 Jones
* select lpad(name,"x",3), lpad(name,'.'), lpad(name,".",3) from loans;
LPAD(name,  'x', 3)   LPAD(name,  '.')      LPAD(name,  '.', 3)

xxxMosca              ....................  ...Mosca
                      Mosca
xxxJones              ....................  ...Jones
                      Jones
xxxKilroy             ...................K  ...Kilroy
                      ilroy
xxxWladislaw          ................Wlad  ...Wladislaw
                      islaw
xxxJones              ....................  ...Jones
                      Jones
xxxMosca              ....................  ...Mosca
                      Mosca
xxxWladislaw          ................Wlad  ...Wladislaw
                      islaw
xxxPeterson           .................Pet  ...Peterson
                      erson
xxxWladislaw          ................Wlad  ...Wladislaw
                      islaw
xxxJones              ....................  ...Jones
                      Jones
* select rpad(name," "),  rpad(name,"x"), rpad(name,'yz') from loans;
RPAD(name,  ' ')      RPAD(name,  'x')      RPAD(name,  'yz')

Mosca                 Moscaxxxxxxxxxxxxxxx  Moscayzyzyzyzyzyzyzy
                      xxxxx                 zyzyz
Jones                 Jonesxxxxxxxxxxxxxxx  Jonesyzyzyzyzyzyzyzy
                      xxxxx                 zyzyz
Kilroy                Kilroyxxxxxxxxxxxxxx  Kilroyyzyzyzyzyzyzyz
                      xxxxx                 yzyz
Wladislaw             Wladislawxxxxxxxxxxx  Wladislawyzyzyzyzyzy
                      xxxxx                 zyzyz
Jones                 Jonesxxxxxxxxxxxxxxx  Jonesyzyzyzyzyzyzyzy
                      xxxxx                 zyzyz
Mosca                 Moscaxxxxxxxxxxxxxxx  Moscayzyzyzyzyzyzyzy
                      xxxxx                 zyzyz
Wladislaw             Wladislawxxxxxxxxxxx  Wladislawyzyzyzyzyzy
                      xxxxx                 zyzyz
Peterson              Petersonxxxxxxxxxxxx  Petersonyzyzyzyzyzyz
                      xxxxx                 yzyz
Wladislaw             Wladislawxxxxxxxxxxx  Wladislawyzyzyzyzyzy
                      xxxxx                 zyzyz
Jones                 Jonesxxxxxxxxxxxxxxx  Jonesyzyzyzyzyzyzyzy
                      xxxxx                 zyzyz
* select rpad(name,"x",3), rpad(name,'.'), rpad(name,".",3) from loans;
RPAD(name,  'x', 3)   RPAD(name,  '.')      RPAD(name,  '.', 3)

Moscaxxx              Mosca...............  Mosca...
                      .....
Jonesxxx              Jones...............  Jones...
                      .....
Kilroyxxx             Kilroy..............  Kilroy...
                      .....
Wladislawxxx          Wladislaw...........  Wladislaw...
                      .....
Jonesxxx              Jones...............  Jones...
                      .....
Moscaxxx              Mosca...............  Mosca...
                      .....
Wladislawxxx          Wladislaw...........  Wladislaw...
                      .....
Petersonxxx           Peterson............  Peterson...
                      .....
Wladislawxxx          Wladislaw...........  Wladislaw...
                      .....
Jonesxxx              Jones...............  Jones...
                      .....
* select ltrim("raebggleave","garbage") from emt;
LTRIM( 'raebggleave',  'garbage')

leave
* select rtrim("raebggleaveegabrag","garbage") from emt;
RTRIM( 'raebggleaveegabrag',  'garbage')

raebggleav
* select rtrim("Horatio     ") concat " " concat ltrim("    Hornblower") from emt;
((RTRIM( 'Horatio     ') concat  ' ') concat LTRIM( '    Hornblower'))

Horatio Hornblower
* select strdel(name, lscan(name, 'on'), 3) from loans where 
.   lscan(name, 'on') != 0;
STRDEL(name, LSCAN(name,  'on'), 3)

Js
Js
Peters
Js
* update loans set name = toupper(name) where
.   name = ltrim(rtrim(name, substr('qrst', 3)), substr('LMNO', 2));
* select name from loans;
name

Mosca
Jones
KILROY
WLADISLAW
Jones
Mosca
WLADISLAW
PETERSON
WLADISLAW
Jones
* update loans set name  strins(name, rscan(tolower(name), 'on'),
.   lpad(rpad(':', '<-', 2), '->', 2)) where
.   rscan(tolower(name), 'on') != 0;
* select name from loans;
name

Mosca
J->->:<-<-ones
KILROY
WLADISLAW
J->->:<-<-ones
Mosca
WLADISLAW
PETERS->->:<-<-ON
WLADISLAW
J->->:<-<-ones
* select rscan("abcdab","ab",2) from emt;
RSCAN( 'abcdab',  'ab', 2)

                         1
* drop emt;
* create bt (b1 bulk(10,5,5), b2 bulk(8,4,4));
* insert into bt set to '010203040506', '1122330055';
* insert into bt set to 'ffee00ddffee', 'ffee';
* select b1, substr(b1, 3), substr(b1,3,2) from bt;
b1                             SUBSTR(b1, 3)         SUBSTR(b1, 3, 2)

01 02 03 04 05 06              03 04 05 06           03 04
ff ee 00 dd ff ee              00 dd ff ee           00 dd
* select b1,b2, b2 concat b1 from bt;
b1                             b2                       (b2 concat b1)

01 02 03 04 05 06              11 22 33 00 55           11 22 33 00 55 01 02
                                                         03 04 05 06
ff ee 00 dd ff ee              ff ee                    ff ee ff ee 00 dd ff
                                                         ee
* select lscan(b1,b2), rscan(b1,b2) from bt;
LSCAN(b1, b2)  RSCAN(b1, b2)

            0              0
            1              5
* select lscan(b1,b2,2), rscan(b1,b2,2) from bt;
LSCAN(b1, b2, 2)  RSCAN(b1, b2, 2)

               0                 0
               5                 1
* select lscan(b1,b2,3), rscan(b1,b2,3) from bt;
LSCAN(b1, b2, 3)  RSCAN(b1, b2, 3)

               0                 0
               0                 0
* select lscan(b1 concat b2,b2), rscan(b1 concat b2,b2) from bt;
LSCAN((b1 concat b2), b2)  RSCAN((b1 concat b2), b2)

                        7                          7
                        1                          7
* select strdel(b1,3,2), b1, strdel(b2,2,3), b2 from bt; 
STRDEL(b1, 3, 2)      b1                             STRDEL(b2, 2, 3)      b2

01 02 05 06           01 02 03 04 05 06              11 55                 11 22 33 00 55
ff ee ff ee           ff ee 00 dd ff ee              ff                    ff ee
* drop bt;
* 
+-+-+-+-+-END-OF-FILE-+-+-+-+-+
chmod 644 stdout
cat > tag <<'+-+-+-+-+-END-OF-FILE-+-+-+-+-+'
Test string function expressions: substr(), lscan(), strdel(), strins(), etc.
+-+-+-+-+-END-OF-FILE-+-+-+-+-+
chmod 644 tag
