Re: camelCase UDTF from Neils Liisberg not working on 7.3 -- MIDRANGE-L

Hi, Neil,
Or just document that it needs 5722-SS1 Option 39, International Components for Unicode.
This option does not "cost extra" and comes with the "base" IBM i operating system, and should be on the installation DVD images. :-)
All the best,
Mark S. Waterbury
On Tuesday, March 25, 2025 at 12:10:06 AM EDT, Niels Liisberg <nli@xxxxxxxxxxxxxxxxx> wrote:

It is the regex that uses Unicode conversion,

My solution to you will be- simply remove all the special regex stuff we
have to deal with in Scandinavia

tirs. 25. mar. 2025 kl. 10.17 skrev Don Brown via MIDRANGE-L <
midrange-l@xxxxxxxxxxxxxxxxxx>:

I created the UDTF from Neils and it worked on a 7.4 box. (I have included
the procedure below.)

I compiled on a 7.3 partition successfully but received an error when I
tried to run it about a missing QICU library.

I searched for an installation method for this library and failed to find
anything.

I saved this library from another 7.3 partition and restored on the
partition where I was testing the UDTF.

Now the functions runs but the results are not correct. Neils provided an
example as follows.

-- usecase
values ( qusrsys.camel_case('Saldo beløb'));

On 7.4 this returns ... saldoBeloeb

On 7.3 this returns ... <Result is blank>

I am not seeing any errors in the log.

Visual explain provided no errors.

Any suggestions greatly appreciated.

Thanks
Don

-- UDTF to return a camelcase of a string for column naming purposes

-- Simply paste this gist into ACS SQL and run it to create the UDTF.

-- Note: I am using library QUSRSYS. I suggest you put it into your own
tool library

-- It is a cool example how far you can go with SQL: Have fun -
-- (C) Niels Liisberg 2021

-- This gist is distributed on an "as is" basis, without warranties
-- or conditions of any kind, either express or implied.

----------------------------------------------------------------------------------------------
create or replace function qusrsys.camel_case (
name varchar(128)
)
returns varchar(128)
no external action
set option output=*print, commit=*none, dbgview = *list
begin
declare temp varchar(128);
declare outString varchar(128);
declare i int;
declare upperNext int;
declare c char(1);

-- First snake-case the name to sanitise it AND it will also works if
the string is already snake-case
set temp = lower(name);
set temp = regexp_replace(temp , '[æ]' , 'ae');
set temp = regexp_replace(temp , '[ø@]' , 'oe');
set temp = regexp_replace(temp , '[å]' , 'aa');
set temp = regexp_replace(temp , '[^a-z0-9]' , ' ');
set temp = trim(temp); -- Done here if the name has trailing invalid
chars
set temp = regexp_replace(temp , ' +', '_');

-- Columns names can not begin with a digit
if substring(temp , 1 , 1) >= '0' then
set temp = 'x' concat temp;
end if;

set i = 1;
set upperNext = 0;
set outString = '';
while i <= length(temp) do
set c = substr(temp , i ,1);
if c = '_' then
set upperNext = 1;
elseif upperNext = 1 then
set outString = outString || upper(c);
set upperNext = 0;
else
set outString = outString || c;
end if;
set i = i +1;
end while;
return outString;
end;

-- usecase
values ( qusrsys.camel_case('Saldo beløb'));

Brisbane - Sydney - Melbourne

Don Brown

Senior Consultant

P: 1300 088 400

DISCLAIMER. Before opening any attachments, check them for viruses and
defects. This email and its attachments may contain confidential
information. If you are not the intended recipient, please do not read,
distribute or copy this email or its attachments but notify sender and
delete it. Any views expressed in this email are those of the individual
sender
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.