A question about how to code a SQL function

Larry Kirkpatrick

A question about how to code a SQL function

I am attempting to code a rather simple function that just takes an input CLOB and replaces substrings of two or more spaces to be only a single space (basically to normalize the text for comparison purposes).

I could do this coding a COBOL function and it would be fairly straight forward.  

pseudo code would be:

SET OUTPUT_CLOB = '';

SET input-byte-pointer = 1;

while input-byte-pointer <= length(input_clob)

IF NOT next two characters of input = '  '

SET next character of OUTPUT to next byte of input

end-if

increment input-byte-pointer

next iteration of while loop

 

I would really like to code this as a SQL function.  I have done this successfully (but with bad performance) and here is the body of my code:

SET TEXT_OUT = '' ;
SET IN_TEXT_PTR = 1;

PARSE: LOOP

SET WRK_2_BYTES = SUBSTR(TEXT_IN, IN_TEXT_PTR, 2);
IF IN_TEXT_PTR = LENGTH(TEXT_IN)
THEN
IF SUBSTR(WRK_2_BYTES, 1, 1) = ' '
THEN
LEAVE PARSE;
END IF;
SET TEXT_OUT = TEXT_OUT
CONCAT SUBSTR(WRK_2_BYTES,1, 1);
LEAVE PARSE;
END IF;

IF NOT WRK_2_BYTES = ' '
THEN
SET TEXT_OUT = TEXT_OUT
CONCAT SUBSTR(WRK_2_BYTES, 1, 1) ;
END IF;


SET IN_TEXT_PTR = IN_TEXT_PTR + 1;

END LOOP;

 

While this works (functionally), it performs badly.  This is because as each byte is added, the entire string is moved (instead of just placing the single character at the end).

 

Does anyone have any solution for this (other than writing the function as a COBOL or REXX or some other language function)?

 

Larry

Sam Baugh

A question about how to code a SQL function
(in response to Larry Kirkpatrick)
I suppose you could try using a loop while POSITION(str,' ') > 1,
REPLACE(str,' ',' '). REXX does have a SPACE function that will remove
excess spaces in strings.

On Thu, Apr 30, 2020 at 9:45 AM Larry Kirkpatrick <[login to unmask email]>
wrote:

> I am attempting to code a rather simple function that just takes an input
> CLOB and replaces substrings of two or more spaces to be only a single
> space (basically to normalize the text for comparison purposes).
>
> I could do this coding a COBOL function and it would be fairly straight
> forward.
>
> pseudo code would be:
>
> SET OUTPUT_CLOB = '';
>
> SET input-byte-pointer = 1;
>
> while input-byte-pointer <= length(input_clob)
>
> IF NOT next two characters of input = ' '
>
> SET next character of OUTPUT to next byte of input
>
> end-if
>
> increment input-byte-pointer
>
> next iteration of while loop
>
>
>
> I would really like to code this as a SQL function. I have done this
> successfully (but with bad performance) and here is the body of my code:
>
> SET TEXT_OUT = '' ;
> SET IN_TEXT_PTR = 1;
>
> PARSE: LOOP
>
> SET WRK_2_BYTES = SUBSTR(TEXT_IN, IN_TEXT_PTR, 2);
> IF IN_TEXT_PTR = LENGTH(TEXT_IN)
> THEN
> IF SUBSTR(WRK_2_BYTES, 1, 1) = ' '
> THEN
> LEAVE PARSE;
> END IF;
> SET TEXT_OUT = TEXT_OUT
> CONCAT SUBSTR(WRK_2_BYTES,1, 1);
> LEAVE PARSE;
> END IF;
>
> IF NOT WRK_2_BYTES = ' '
> THEN
> SET TEXT_OUT = TEXT_OUT
> CONCAT SUBSTR(WRK_2_BYTES, 1, 1) ;
> END IF;
>
>
> SET IN_TEXT_PTR = IN_TEXT_PTR + 1;
>
> END LOOP;
>
>
>
> While this works (functionally), it performs badly. This is because as
> each byte is added, the entire string is moved (instead of just placing the
> single character at the end).
>
>
>
> Does anyone have any solution for this (other than writing the function as
> a COBOL or REXX or some other language function)?
>
>
>
> Larry
>
> -----End Original Message-----
>

Sam Baugh

A question about how to code a SQL function
(in response to Larry Kirkpatrick)
Found this from a Craig Mullins article, 'Removing Superfluous Spaces'

select '>>'
|| strip(
replace(
replace(
replace(' What about this or that
?',space(1),'<>')
,'><',space(0))
,'<>',space(1))
)
||'<<'
from sysibm.sysdummy1 with ur

On Thu, Apr 30, 2020 at 9:45 AM Larry Kirkpatrick <[login to unmask email]>
wrote:

> I am attempting to code a rather simple function that just takes an input
> CLOB and replaces substrings of two or more spaces to be only a single
> space (basically to normalize the text for comparison purposes).
>
> I could do this coding a COBOL function and it would be fairly straight
> forward.
>
> pseudo code would be:
>
> SET OUTPUT_CLOB = '';
>
> SET input-byte-pointer = 1;
>
> while input-byte-pointer <= length(input_clob)
>
> IF NOT next two characters of input = ' '
>
> SET next character of OUTPUT to next byte of input
>
> end-if
>
> increment input-byte-pointer
>
> next iteration of while loop
>
>
>
> I would really like to code this as a SQL function. I have done this
> successfully (but with bad performance) and here is the body of my code:
>
> SET TEXT_OUT = '' ;
> SET IN_TEXT_PTR = 1;
>
> PARSE: LOOP
>
> SET WRK_2_BYTES = SUBSTR(TEXT_IN, IN_TEXT_PTR, 2);
> IF IN_TEXT_PTR = LENGTH(TEXT_IN)
> THEN
> IF SUBSTR(WRK_2_BYTES, 1, 1) = ' '
> THEN
> LEAVE PARSE;
> END IF;
> SET TEXT_OUT = TEXT_OUT
> CONCAT SUBSTR(WRK_2_BYTES,1, 1);
> LEAVE PARSE;
> END IF;
>
> IF NOT WRK_2_BYTES = ' '
> THEN
> SET TEXT_OUT = TEXT_OUT
> CONCAT SUBSTR(WRK_2_BYTES, 1, 1) ;
> END IF;
>
>
> SET IN_TEXT_PTR = IN_TEXT_PTR + 1;
>
> END LOOP;
>
>
>
> While this works (functionally), it performs badly. This is because as
> each byte is added, the entire string is moved (instead of just placing the
> single character at the end).
>
>
>
> Does anyone have any solution for this (other than writing the function as
> a COBOL or REXX or some other language function)?
>
>
>
> Larry
>
> -----End Original Message-----
>

Daniel Luksetich

A question about how to code a SQL function
(in response to Sam Baugh)
I don’t think that will work against a CLOB.

Dan



+--------------------------------------+-----------------------------------------------------------+

| Daniel L Luksetich | IBM Certified Advanced Database Administrator – |

| IBM GOLD Consultant | Db2 10.1 for Linux UNIX and Windows |

| IDUG Content Committee Past-Chairman | IBM Certified Database Adminstrator – Db2 12 for z/OS |

| IDUG DB2-L Administrator | IBM Certified System Administrator – Db2 11 for z/OS |

| URL: https://db2expert.com https://db2expert.com | IBM Certified Application Developer – Db2 11 for z/OS |

+--------------------------------------+-----------------------------------------------------------+





From: Sam Baugh <[login to unmask email]>
Sent: Thursday, April 30, 2020 10:50 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: A question about how to code a SQL function



Found this from a Craig Mullins article, 'Removing Superfluous Spaces'



select '>>'
|| strip(
replace(
replace(
replace(' What about this or that ?',space(1),'<>')
,'><',space(0))
,'<>',space(1))
)
||'<<'
from sysibm.sysdummy1 with ur



On Thu, Apr 30, 2020 at 9:45 AM Larry Kirkpatrick <[login to unmask email] <mailto:[login to unmask email]> > wrote:

I am attempting to code a rather simple function that just takes an input CLOB and replaces substrings of two or more spaces to be only a single space (basically to normalize the text for comparison purposes).

I could do this coding a COBOL function and it would be fairly straight forward.

pseudo code would be:

SET OUTPUT_CLOB = '';

SET input-byte-pointer = 1;

while input-byte-pointer <= length(input_clob)

IF NOT next two characters of input = ' '

SET next character of OUTPUT to next byte of input

end-if

increment input-byte-pointer

next iteration of while loop



I would really like to code this as a SQL function. I have done this successfully (but with bad performance) and here is the body of my code:

SET TEXT_OUT = '' ;
SET IN_TEXT_PTR = 1;

PARSE: LOOP

SET WRK_2_BYTES = SUBSTR(TEXT_IN, IN_TEXT_PTR, 2);
IF IN_TEXT_PTR = LENGTH(TEXT_IN)
THEN
IF SUBSTR(WRK_2_BYTES, 1, 1) = ' '
THEN
LEAVE PARSE;
END IF;
SET TEXT_OUT = TEXT_OUT
CONCAT SUBSTR(WRK_2_BYTES,1, 1);
LEAVE PARSE;
END IF;

IF NOT WRK_2_BYTES = ' '
THEN
SET TEXT_OUT = TEXT_OUT
CONCAT SUBSTR(WRK_2_BYTES, 1, 1) ;
END IF;


SET IN_TEXT_PTR = IN_TEXT_PTR + 1;

END LOOP;



While this works (functionally), it performs badly. This is because as each byte is added, the entire string is moved (instead of just placing the single character at the end).



Does anyone have any solution for this (other than writing the function as a COBOL or REXX or some other language function)?



Larry



-----End Original Message-----



-----End Original Message-----

Attachments

  • image001.png (9.6k)
  • image002.png (14k)
  • image003.png (10.4k)
  • image004.png (11.7k)
  • image005.png (13.7k)
  • image006.png (13.1k)
  • image007.png (14.2k)
  • image008.png (7.5k)

Sam Baugh

A question about how to code a SQL function
(in response to Daniel Luksetich)
Rats! Overlooked the LOB requirement. But its nice for everything else.

On Thu, Apr 30, 2020 at 11:08 AM Daniel L Luksetich <[login to unmask email]>
wrote:

> I don’t think that will work against a CLOB.
>
> Dan
>
>
>
>
> +--------------------------------------+-----------------------------------------------------------+
>
> | Daniel L Luksetich | IBM Certified Advanced Database
> Administrator – |
>
> | IBM GOLD Consultant | Db2 10.1 for Linux UNIX and
> Windows |
>
> | IDUG Content Committee Past-Chairman | IBM Certified Database
> Adminstrator – Db2 12 for z/OS |
>
> | IDUG DB2-L Administrator | IBM Certified System
> Administrator – Db2 11 for z/OS |
>
> | URL: https://db2expert.com | IBM Certified Application
> Developer – Db2 11 for z/OS |
>
>
> +--------------------------------------+-----------------------------------------------------------+
>
>
>
>
>
> *From:* Sam Baugh <[login to unmask email]>
> *Sent:* Thursday, April 30, 2020 10:50 AM
> *To:* [login to unmask email]
> *Subject:* [DB2-L] - RE: A question about how to code a SQL function
>
>
>
> Found this from a Craig Mullins article, 'Removing Superfluous Spaces'
>
>
>
> select '>>'
> || strip(
> replace(
> replace(
> replace(' What about this or that
> ?',space(1),'<>')
> ,'><',space(0))
> ,'<>',space(1))
> )
> ||'<<'
> from sysibm.sysdummy1 with ur
>
>
>
> On Thu, Apr 30, 2020 at 9:45 AM Larry Kirkpatrick <[login to unmask email]>
> wrote:
>
> I am attempting to code a rather simple function that just takes an input
> CLOB and replaces substrings of two or more spaces to be only a single
> space (basically to normalize the text for comparison purposes).
>
> I could do this coding a COBOL function and it would be fairly straight
> forward.
>
> pseudo code would be:
>
> SET OUTPUT_CLOB = '';
>
> SET input-byte-pointer = 1;
>
> while input-byte-pointer <= length(input_clob)
>
> IF NOT next two characters of input = ' '
>
> SET next character of OUTPUT to next byte of input
>
> end-if
>
> increment input-byte-pointer
>
> next iteration of while loop
>
>
>
> I would really like to code this as a SQL function. I have done this
> successfully (but with bad performance) and here is the body of my code:
>
> SET TEXT_OUT = '' ;
> SET IN_TEXT_PTR = 1;
>
> PARSE: LOOP
>
> SET WRK_2_BYTES = SUBSTR(TEXT_IN, IN_TEXT_PTR, 2);
> IF IN_TEXT_PTR = LENGTH(TEXT_IN)
> THEN
> IF SUBSTR(WRK_2_BYTES, 1, 1) = ' '
> THEN
> LEAVE PARSE;
> END IF;
> SET TEXT_OUT = TEXT_OUT
> CONCAT SUBSTR(WRK_2_BYTES,1, 1);
> LEAVE PARSE;
> END IF;
>
> IF NOT WRK_2_BYTES = ' '
> THEN
> SET TEXT_OUT = TEXT_OUT
> CONCAT SUBSTR(WRK_2_BYTES, 1, 1) ;
> END IF;
>
>
> SET IN_TEXT_PTR = IN_TEXT_PTR + 1;
>
> END LOOP;
>
>
>
> While this works (functionally), it performs badly. This is because as
> each byte is added, the entire string is moved (instead of just placing the
> single character at the end).
>
>
>
> Does anyone have any solution for this (other than writing the function as
> a COBOL or REXX or some other language function)?
>
>
>
> Larry
>
>
> -----End Original Message-----
>
>
> -----End Original Message-----
>
Attachments

  • image001.png (9.6k)
  • image002.png (14k)
  • image003.png (10.4k)
  • image004.png (11.7k)
  • image005.png (13.7k)
  • image006.png (13.1k)
  • image007.png (14.2k)
  • image008.png (7.5k)

Sam Baugh

A question about how to code a SQL function
(in response to Sam Baugh)
I think this works, may be a little crude, it breaks the CLOB input into
VARCHAR pieces of 16,000, uses the REPLACE functions, and concatenates to
the CLOB output. Accounts for whether the prior and next piece should have
a space between them.

--aqt options,sdelim=#
--drop function sam.whitespace#
--create function sam.whitespace
alter function sam.whitespace replace
( p_in clob
)
returns clob
begin
declare sqlcode integer;
declare v_out clob default '';
declare v_start integer default 1;
declare v_beg char(1) default '';
declare v_end char(1) default '';
declare v_last char(1) default '';
declare v_txt varchar(32704);
while v_start <= length(p_in) do
set v_txt = substr(p_in,v_start,min(length(p_in),16000));
if substr(v_txt,1,1) = '' then
set v_beg = '';
else
set v_beg = 'X';
end if;
if substr(v_txt,length(v_txt),1) = '' then
set v_end = '';
else
set v_end = 'X';
end if;
set v_txt =
strip(replace(replace(replace(v_txt,space(1),'<>'),'><',space(0)),'<>',space(1)));
if v_last = 'X' and v_beg = 'X' then
set v_out = v_out || v_txt;
else
if v_start = 1 then
set v_out = v_txt;
else
set v_out = v_out || ' ' || v_txt;
end if;
end if;
set v_last = v_end;
set v_start = v_start + 16000;
end while;
return v_out;
end#

On Thu, Apr 30, 2020 at 11:15 AM Sam Baugh <[login to unmask email]> wrote:

> Rats! Overlooked the LOB requirement. But its nice for everything else.
>
> On Thu, Apr 30, 2020 at 11:08 AM Daniel L Luksetich <[login to unmask email]>
> wrote:
>
>> I don’t think that will work against a CLOB.
>>
>> Dan
>>
>>
>>
>>
>> +--------------------------------------+-----------------------------------------------------------+
>>
>> | Daniel L Luksetich | IBM Certified Advanced Database
>> Administrator – |
>>
>> | IBM GOLD Consultant | Db2 10.1 for Linux UNIX and
>> Windows |
>>
>> | IDUG Content Committee Past-Chairman | IBM Certified Database
>> Adminstrator – Db2 12 for z/OS |
>>
>> | IDUG DB2-L Administrator | IBM Certified System
>> Administrator – Db2 11 for z/OS |
>>
>> | URL: https://db2expert.com | IBM Certified Application
>> Developer – Db2 11 for z/OS |
>>
>>
>> +--------------------------------------+-----------------------------------------------------------+
>>
>>
>>
>>
>>
>> *From:* Sam Baugh <[login to unmask email]>
>> *Sent:* Thursday, April 30, 2020 10:50 AM
>> *To:* [login to unmask email]
>> *Subject:* [DB2-L] - RE: A question about how to code a SQL function
>>
>>
>>
>> Found this from a Craig Mullins article, 'Removing Superfluous Spaces'
>>
>>
>>
>> select '>>'
>> || strip(
>> replace(
>> replace(
>> replace(' What about this or that
>> ?',space(1),'<>')
>> ,'><',space(0))
>> ,'<>',space(1))
>> )
>> ||'<<'
>> from sysibm.sysdummy1 with ur
>>
>>
>>
>> On Thu, Apr 30, 2020 at 9:45 AM Larry Kirkpatrick <[login to unmask email]>
>> wrote:
>>
>> I am attempting to code a rather simple function that just takes an input
>> CLOB and replaces substrings of two or more spaces to be only a single
>> space (basically to normalize the text for comparison purposes).
>>
>> I could do this coding a COBOL function and it would be fairly straight
>> forward.
>>
>> pseudo code would be:
>>
>> SET OUTPUT_CLOB = '';
>>
>> SET input-byte-pointer = 1;
>>
>> while input-byte-pointer <= length(input_clob)
>>
>> IF NOT next two characters of input = ' '
>>
>> SET next character of OUTPUT to next byte of input
>>
>> end-if
>>
>> increment input-byte-pointer
>>
>> next iteration of while loop
>>
>>
>>
>> I would really like to code this as a SQL function. I have done this
>> successfully (but with bad performance) and here is the body of my code:
>>
>> SET TEXT_OUT = '' ;
>> SET IN_TEXT_PTR = 1;
>>
>> PARSE: LOOP
>>
>> SET WRK_2_BYTES = SUBSTR(TEXT_IN, IN_TEXT_PTR, 2);
>> IF IN_TEXT_PTR = LENGTH(TEXT_IN)
>> THEN
>> IF SUBSTR(WRK_2_BYTES, 1, 1) = ' '
>> THEN
>> LEAVE PARSE;
>> END IF;
>> SET TEXT_OUT = TEXT_OUT
>> CONCAT SUBSTR(WRK_2_BYTES,1, 1);
>> LEAVE PARSE;
>> END IF;
>>
>> IF NOT WRK_2_BYTES = ' '
>> THEN
>> SET TEXT_OUT = TEXT_OUT
>> CONCAT SUBSTR(WRK_2_BYTES, 1, 1) ;
>> END IF;
>>
>>
>> SET IN_TEXT_PTR = IN_TEXT_PTR + 1;
>>
>> END LOOP;
>>
>>
>>
>> While this works (functionally), it performs badly. This is because as
>> each byte is added, the entire string is moved (instead of just placing the
>> single character at the end).
>>
>>
>>
>> Does anyone have any solution for this (other than writing the function
>> as a COBOL or REXX or some other language function)?
>>
>>
>>
>> Larry
>>
>>
>> -----End Original Message-----
>>
>>
>> -----End Original Message-----
>

Suresh Sane

A question about how to code a SQL function
(in response to Daniel Luksetich)
Even for a small clob (up to 254 bytes)?

Thx
Suresh

________________________________
From: Daniel L Luksetich <[login to unmask email]>
Sent: Thursday, April 30, 2020 4:07 PM
To: [login to unmask email] <[login to unmask email]>
Subject: [DB2-L] - RE: A question about how to code a SQL function


I don’t think that will work against a CLOB.

Dan



+--------------------------------------+-----------------------------------------------------------+

| Daniel L Luksetich | IBM Certified Advanced Database Administrator – |

| IBM GOLD Consultant | Db2 10.1 for Linux UNIX and Windows |

| IDUG Content Committee Past-Chairman | IBM Certified Database Adminstrator – Db2 12 for z/OS |

| IDUG DB2-L Administrator | IBM Certified System Administrator – Db2 11 for z/OS |

| URL: https://db2expert.com https://db2expert.com | IBM Certified Application Developer – Db2 11 for z/OS |

+--------------------------------------+-----------------------------------------------------------+

[cid:[login to unmask email][cid:[login to unmask email][cid:[login to unmask email][cid:[login to unmask email] [cid:[login to unmask email] [cid:[login to unmask email] [cid:[login to unmask email] [cid:[login to unmask email]



From: Sam Baugh <[login to unmask email]>
Sent: Thursday, April 30, 2020 10:50 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: A question about how to code a SQL function



Found this from a Craig Mullins article, 'Removing Superfluous Spaces'



select '>>'
|| strip(
replace(
replace(
replace(' What about this or that ?',space(1),'<>')
,'><',space(0))
,'<>',space(1))
)
||'<<'
from sysibm.sysdummy1 with ur



On Thu, Apr 30, 2020 at 9:45 AM Larry Kirkpatrick <[login to unmask email]<mailto:[login to unmask email]>> wrote:

I am attempting to code a rather simple function that just takes an input CLOB and replaces substrings of two or more spaces to be only a single space (basically to normalize the text for comparison purposes).

I could do this coding a COBOL function and it would be fairly straight forward.

pseudo code would be:

SET OUTPUT_CLOB = '';

SET input-byte-pointer = 1;

while input-byte-pointer <= length(input_clob)

IF NOT next two characters of input = ' '

SET next character of OUTPUT to next byte of input

end-if

increment input-byte-pointer

next iteration of while loop



I would really like to code this as a SQL function. I have done this successfully (but with bad performance) and here is the body of my code:

SET TEXT_OUT = '' ;
SET IN_TEXT_PTR = 1;

PARSE: LOOP

SET WRK_2_BYTES = SUBSTR(TEXT_IN, IN_TEXT_PTR, 2);
IF IN_TEXT_PTR = LENGTH(TEXT_IN)
THEN
IF SUBSTR(WRK_2_BYTES, 1, 1) = ' '
THEN
LEAVE PARSE;
END IF;
SET TEXT_OUT = TEXT_OUT
CONCAT SUBSTR(WRK_2_BYTES,1, 1);
LEAVE PARSE;
END IF;

IF NOT WRK_2_BYTES = ' '
THEN
SET TEXT_OUT = TEXT_OUT
CONCAT SUBSTR(WRK_2_BYTES, 1, 1) ;
END IF;

SET IN_TEXT_PTR = IN_TEXT_PTR + 1;

END LOOP;



While this works (functionally), it performs badly. This is because as each byte is added, the entire string is moved (instead of just placing the single character at the end).



Does anyone have any solution for this (other than writing the function as a COBOL or REXX or some other language function)?



Larry



-----End Original Message-----

-----End Original Message-----
Attachments

  • image001.png (9.6k)
  • image002.png (14k)
  • image003.png (10.4k)
  • image004.png (11.7k)
  • image005.png (13.7k)
  • image006.png (13.1k)
  • image007.png (14.2k)
  • image008.png (7.5k)

Daniel Luksetich

A question about how to code a SQL function
(in response to Suresh Sane)
If the CLOB is a varchar(254) it is allowed, but not against LOB values.

Dan



+--------------------------------------+------------------------------------
-----------------------+

| Daniel L Luksetich | IBM Certified Advanced Database
Administrator - |

| IBM GOLD Consultant | Db2 10.1 for Linux UNIX and
Windows |

| IDUG Content Committee Past-Chairman | IBM Certified Database Adminstrator
- Db2 12 for z/OS |

| IDUG DB2-L Administrator | IBM Certified System Administrator
- Db2 11 for z/OS |

| URL: https://db2expert.com https://db2expert.com | IBM
Certified Application Developer - Db2 11 for z/OS |

+--------------------------------------+------------------------------------
-----------------------+





From: suresh sane <[login to unmask email]>
Sent: Thursday, April 30, 2020 2:08 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: A question about how to code a SQL function



Even for a small clob (up to 254 bytes)?



Thx

Suresh



_____

From: Daniel L Luksetich <[login to unmask email] <mailto:[login to unmask email]>
>
Sent: Thursday, April 30, 2020 4:07 PM
To: [login to unmask email] <mailto:[login to unmask email]>
<[login to unmask email] <mailto:[login to unmask email]> >
Subject: [DB2-L] - RE: A question about how to code a SQL function



I don't think that will work against a CLOB.

Dan



+--------------------------------------+------------------------------------
-----------------------+

| Daniel L Luksetich | IBM Certified Advanced Database
Administrator - |

| IBM GOLD Consultant | Db2 10.1 for Linux UNIX and
Windows |

| IDUG Content Committee Past-Chairman | IBM Certified Database Adminstrator
- Db2 12 for z/OS |

| IDUG DB2-L Administrator | IBM Certified System Administrator
- Db2 11 for z/OS |

| URL: https://db2expert.com https://db2expert.com | IBM
Certified Application Developer - Db2 11 for z/OS |

+--------------------------------------+------------------------------------
-----------------------+





From: Sam Baugh <[login to unmask email] <mailto:[login to unmask email]> >
Sent: Thursday, April 30, 2020 10:50 AM
To: [login to unmask email] <mailto:[login to unmask email]>
Subject: [DB2-L] - RE: A question about how to code a SQL function



Found this from a Craig Mullins article, 'Removing Superfluous Spaces'



select '>>'
|| strip(
replace(
replace(
replace(' What about this or that
?',space(1),'<>')
,'><',space(0))
,'<>',space(1))
)
||'<<'
from sysibm.sysdummy1 with ur



On Thu, Apr 30, 2020 at 9:45 AM Larry Kirkpatrick <[login to unmask email]
<mailto:[login to unmask email]> > wrote:

I am attempting to code a rather simple function that just takes an input
CLOB and replaces substrings of two or more spaces to be only a single space
(basically to normalize the text for comparison purposes).

I could do this coding a COBOL function and it would be fairly straight
forward.

pseudo code would be:

SET OUTPUT_CLOB = '';

SET input-byte-pointer = 1;

while input-byte-pointer <= length(input_clob)

IF NOT next two characters of input = ' '

SET next character of OUTPUT to next byte of input

end-if

increment input-byte-pointer

next iteration of while loop



I would really like to code this as a SQL function. I have done this
successfully (but with bad performance) and here is the body of my code:

SET TEXT_OUT = '' ;
SET IN_TEXT_PTR = 1;

PARSE: LOOP

SET WRK_2_BYTES = SUBSTR(TEXT_IN, IN_TEXT_PTR, 2);
IF IN_TEXT_PTR = LENGTH(TEXT_IN)
THEN
IF SUBSTR(WRK_2_BYTES, 1, 1) = ' '
THEN
LEAVE PARSE;
END IF;
SET TEXT_OUT = TEXT_OUT
CONCAT SUBSTR(WRK_2_BYTES,1, 1);
LEAVE PARSE;
END IF;

IF NOT WRK_2_BYTES = ' '
THEN
SET TEXT_OUT = TEXT_OUT
CONCAT SUBSTR(WRK_2_BYTES, 1, 1) ;
END IF;


SET IN_TEXT_PTR = IN_TEXT_PTR + 1;

END LOOP;



While this works (functionally), it performs badly. This is because as each
byte is added, the entire string is moved (instead of just placing the
single character at the end).



Does anyone have any solution for this (other than writing the function as a
COBOL or REXX or some other language function)?



Larry



-----End Original Message-----



-----End Original Message-----



-----End Original Message-----

Attachments

  • image001.png (9.6k)
  • image002.png (14k)
  • image003.png (10.4k)
  • image004.png (11.7k)
  • image005.png (13.7k)
  • image006.png (13.1k)
  • image007.png (14.2k)
  • image008.png (7.5k)

Michael Hannan

RE: A question about how to code a SQL function
(in response to Sam Baugh)

In Reply to Sam Baugh:

I think this works, may be a little crude, it breaks the CLOB input into
VARCHAR pieces of 16,000, uses the REPLACE functions, and concatenates to
the CLOB output. Accounts for whether the prior and next piece should have
a space between them.
Etc.
>

Sam,

I like this trick! For anyone that did not understand. All blanks changed to <>, then all >< to nothing leaving only at most one <> to change back to blank.

I am not going to check if your function works! Instead of using '<>' I would be tempted to go for something even less likely to appear in the text, like X'00FF' as wild examples.

The process could also be done in a recursive SQL, picking off substrings to process, and later putting them back onto output string CLOB. Special check for boundary blanks at the split. I have no idea which way would perform better. Perhaps the function?

There will be other variations, on the theme that could work well also, perhaps without initially expanding the string, and allowing processing close to 32K at eat bite.

Michael Hannan,
DB2 Application Performance Specialist