DELETE STRING FROM A CERTAIN POSITION

[Cezar Moniz]

Dear and Dear.

I apologize if the topic has already been addressed, but I have an urgent question and that's why I put it in the group.

I need to change a customer table (full_name field) with the following rule:

delete from the third customer name. Example:

Fulano Augusto Mendes Junior
Change to Fulano Augusto

Beltrano Marques da Silva
Change to Beltrano Marques

and so on.

Thank you all.

--

[]'s

[Tomasz Tyrakowski]

Cezar,
That's not a trivial problem ;). I came up with a simple idea to cut off
everything after the second space (and leave the field intact if it has
less than two spaces).
The query is like this:

update TABLE
set full_name =
iif(
position(' ', full_name, position(' ', full_name) + 1) > 0,

substring(full_name from 1 for position(' ', full_name, position('
', full_name) + 1)),

full_name
)

In case an extra space remains at the end, just trim the value :).
I'm not sure it covers all your cases, but I hope it helps.

cheers
Tomasz

[Mark Rotteveel]

On 24-05-2022 21:26, Cezar Moniz wrote:
> Dear and Dear.
>
> I apologize if the topic has already been addressed, but I have an
> urgent question and that's why I put it in the group.
>
> I need to change a customer table (full_name field) with the following rule:
>
> delete from the third customer name. Example:
>
> Fulano Augusto Mendes Junior
> Change to Fulano Augusto
>
>

> Change to Beltrano Marques
>
> and so on.

How do you determine what the third name is? Is this simply retain the
first two space-separated words, or is there more to it?

In Firebird 2.5 (possibly also earlier version), you can use substring
with using position to determine the second space

substring(name from 1 for position(' ', name, position(' ', name) + 1))

In Firebird 3.0 and higher, you can use substring with a regular expression:

substring(name similar '\"[[:ALPHA:]]+ [[:ALPHA:]]+\"%' escape '\')

However, these solution can be brittle, for example if there are more
than two spaces between names, or only one name, and the regular
expression solution won't work if names contain - for example a dash
(though that could be addressed with expanding the pattern, e.g. using
[[:ALPHA:]\-] for the name).

Especially for the regular expression solution, it is advisable to wrap
it in a coalesce to return the original name as a fallback, as non-matching:

coalesce(substring(name similar '\"[[:ALPHA:]]+ [[:ALPHA:]]+\"%' escape
'\'), name)

Also, the substring with position solution works if there are less than
two spaces because of what seems to be a bug in its implementation:
specifying a length of 0 returns the entire value, instead of an empty
string. This is something that could be fixed in a future version, so is
not something that should be relied on.

Mark
--
Mark Rotteveel

[Mark Rotteveel]

On 25-05-2022 08:58, Mark Rotteveel wrote:
> Also, the substring with position solution works if there are less than
> two spaces because of what seems to be a bug in its implementation:
> specifying a length of 0 returns the entire value, instead of an empty
> string. This is something that could be fixed in a future version, so is
> not something that should be relied on.

Disregard this, I forgot I was working with a CHAR because of literals,
so the value was padded with spaces.

However, that does mean a fallback needs to be arranged for values with
less than three names, otherwise you'll end up with empty names.

A solution could be:

coalesce(substring(trim(name) from 1 for nullif(position(' ', name,
position(' ', name) + 1), 0)), name)

Mark
--
Mark Rotteveel

[Cezar Moniz]

Okay Thomas.
I will test your solution and return here in the group.
Thank you very much.🙏

--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/d83b7652-a9db-1b8d-319e-35a7c9f7270b%40sol-system.pl.

--

[]'s

Cezar Moniz

ce...@rtcm.com.br

RTCM Consultoria - www.rtcm.com.br

 21 999 77 43 73

Rio de Janeiro- Brasilia - São Paulo

_________________________________________________________________________________________________________________

Conteúdo confidencial. Se a mensagem foi recebida por engano, por favor, avise ao remetente e apague-a do computador.

Privileged and confidential. If this message has been received in error, please notify sender and delete immediately.

_________________________________________________________________________________________________________________

[Cezar Moniz]

Mark.
Thanks for your feedback on the reply.
I will test your solution!!! I think it will work without any problems.
Actually, I just want to de-characterize the table names so that I can keep people's names anonymous and I can do a demo.
All names have single spaces. I made sure of it. Also, this solution will only be used once for this purpose.

My best regards to all

--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/7eff864a-88b1-0df0-cf7b-e462451bcf26%40lawinegevaar.nl.

[Tim Crawford]

Maybe this is overkill for the requirement, but perhaps people might be interested in the stored procedure below.
It basically works as the inverse of the 'LIST' clause in sql to return a delimited list one row at a time.
I created it a couple of years ago for something similar to Cezar's requirement.

I think performance is not so great for really large tables/columns,
but as a one off it is okay

Also, I seem to recall it is a bad idea to use a blob as the main parameter
so maybe that should be a varchar. I was using it for multiple things and
got tired of having to change the size of the parameter

Anyway, for what its worth:

Examples using stored procedures below:

create table test_names (fullname varchar(255));
commit;

insert into test_names(fullname) values ('Fulano Augusto Mendes Junior');
insert into test_names(fullname) values ('Beltrano Marques da Silva');
commit;

-- default space separated
select tn.FULLNAME, udlr.LIST_SEQ, udlr.list_val
from test_names tn
left join UTL_DELIMITED_LIST_ROWS(tn.fullname) udlr on 1 = 1;

/*
                           [null]    [null]
Tim Crawford                    1    Tim
Tim Crawford                    2    Crawford
Crawford, Tim                   1    Crawford,
Crawford, Tim                   2    Tim
Fulano Augusto Mendes Junior    1    Fulano
Fulano Augusto Mendes Junior    2    Augusto
Fulano Augusto Mendes Junior    3    Mendes
Fulano Augusto Mendes Junior    4    Junior
Beltrano Marques da Silva       1    Beltrano
Beltrano Marques da Silva       2    Marques
Beltrano Marques da Silva       3    da
Beltrano Marques da Silva       4    Silva
*/

-- comma separated
select tn.FULLNAME, udlr.LIST_SEQ, udlr.list_val
from test_names tn
join UTL_DELIMITED_LIST_ROWS(tn.fullname,',') udlr on 1 = 1
where tn.FULLNAME starting with 'Crawford'

/*
Crawford, Tim    1    Crawford
Crawford, Tim    2    Tim

*/

-- new_name procedure, first two names in the list
select tdl.fullname,
       nn.newname
from      test_names tdl
left join new_name(tdl.fullname, 2) nn on 1 = 1
;

/*
Fullname                     Newname
---------------------------- ------------------
<empty>                      <empty>
Tim Crawford                 Tim Crawford
Crawford, Tim                Crawford, Tim
Fulano Augusto Mendes Junior Fulano Augusto
Beltrano Marques da Silva    Beltrano Marques
*/

-- Update
update test_names tdl
set tdl.fullname =
    (select newname from new_name(tdl.fullname));




set term ^ ;
create or alter procedure UTL_DELIMITED_LIST_ROWS
    (
      delimited_list    blob sub_type text -- makes it flexible, but...
    , delimiter         char(1)             default ' '
    )
RETURNS
    (
    list_Seq    integer,
    list_val    blob sub_type text
    )
as
    /*  Selectable procedure, returns one row per list entry
        To remove duplicate, select UNIQUE list_value without list_seq
        The default delimiter is a space, any character can be used

        Returns one row per list entry
    */
    
DECLARE delimPos   integer DEFAULT 1;
DECLARE emptyStr   varchar(1) default '';
BEGIN
    list_Seq = 1;

    -- remove line feeds, allows for multiline sql statments etc
    delimited_list = replace(delimited_list,ascii_char(13),emptyStr);
    delimited_list = replace(delimited_list,ascii_char(10),emptyStr);
    
    -- get rid of leading trailing blanks, even if blanks
    delimited_list = trim(delimited_list);
    -- add a delimiter at the end to make code simpler
    -- loop stop condition is true even if delimited_list has single blank
    delimited_list = delimited_list || delimiter;
    while (delimited_list <> emptyStr) do
      BEGIN
        delimPos = POSITION(delimiter in delimited_list);
        if (delimPos = 0) THEN delimited_list = emptyStr;
        ELSE
          BEGIN
            list_val = trim(SUBSTRING(delimited_list from 1 FOR delimPos - 1));
            SUSPEND;
            list_Seq = list_Seq + 1;
            delimited_list = SUBSTRING(delimited_list from delimPos + 1);
          END
      END    
    exit;
END^
set term ; ^
commit;

set term ^ ;
create or alter procedure new_name
    (
    oldName varchar(255),
    keepNum integer) -- keep first n items in space delimited list
returns (newName varChar(255)
)
as
DECLARE listseq integer;
DECLARE listval varchar(255);
BEGIN
  newname = '';
  for
    select  udlr.LIST_SEQ, udlr.LIST_VAL
    FROM    UTL_DELIMITED_LIST_ROWS(:oldName) udlr
    INTO    :listSeq, :listVal
    do
      BEGIN
        if (listSeq <= keepNum)
        then newName = newName || ' ' || listVal;
        else break;
      END
    suspend;
    exit;
end^
set term ; ^
commit;

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/CAOnKNuNWWGYvDuYs4Jx3TvmOqi%3DaykfOOy85mRPbk2SSfRL38A%40mail.gmail.com.