Hello Jon,

Something like this one?

create table t1 ( f1 string(100) );

insert into t1 values ('first value

second value

third value');



-- Check 2nd items

select * from t1 where f1 REGEX '^([^\\n]*\\n){1}second value'

I think you get a pattern:

-- First item:

'^([^\\n]*\\n){0}your_value'

-- Second item:

'^([^\\n]*\\n){1}your_value'

...

Hi Ivan,

Hm, I'm not sure. Let's say I have a table with 1000 records, each with a text field containing a list of names, like this

Smith, RR
Jones, A
Martinez, S

I want to search the table for all records in which Martinez is the last name in this field and return it in a cursor. I don't want to retrieve records where the Martinez is not the last name, such as this one

Martinez, S
Gordon, MM

I was thinking of creating a Valentina method that would do the equivalent of (and I'll use Xojo syntax, but I think it's clear)

var a() as string = split(field, "\n" //make an array of names
return a(a.ubound) //return last name

And use this method when searching. Wouldn't that approach work?

But I don't see array manipulation functions in the documentation -- of course I may have missed them.

Another ideas:

CREATE OR REPLACE FUNCTION GetLastStringListItem( inList VarChar, inValue VarChar ) RETURNS BOOLEAN 

BEGIN 

    DECLARE res BOOLEAN default false; 



    DECLARE v_EOL String;

    v_EOL = '

';



    DECLARE items_count LLONG;

    set items_count = List_ItemCount( inList, v_EOL ); 

    IF items_count > 0 THEN

        DECLARE last_item VARCHAR;

    	SET last_item = List_NthItem( inList, items_count, v_EOL );

    	IF last_item = inValue THEN

            res = true;

    	END IF

    END IF



	RETURN res;

END







--

SELECT * FROM t1 WHERE GetLastStringListItem(f1, 'third value') = true;

=list&s[]=itemcount]List functions

Thanks, Ivan, this is interesting. I've found the documentation about lists and the create procedure methods

http://valentina-db.com/docs/dokuwiki/v5/doku.php?id=valentina:vcomponents:vsql:reference:create_procedure#function_examples

But I don't see where to make created procudures, how to create them in Xojo, and where to store them.

Furthermore, I already have Valentina regex searches that find any name in a field with multiple variations (name begins with, name contains, whole name) and I don't want to recreate those searches in a new function just for first/last names.

What I'm really looking for is a way to feed the entire field to a Valentina function/method and have it return just the first or last line, which will be subjected to the regex search.

We have a VText field "names" holds a list of names in each record.

I'd like to make a new temporary field "firstNames", OR use a created function, that returns just the first name in for each record, which is what Valentina uses for the SQL search.

This pseudocode would return all records in which the first name starts with "Jam",

SELECT * FROM t1 WHERE firstNames REGEX 'Jam'

Maybe this can't be done so easily or at all, in which case I'll take a look at post-processing the whole field search and drop the records that don't meet the first/last criterion.

I appreciated your help and patience.

But I don't see where to make created procudures, how to create them in Xojo, and where to store them.

It's db structure's object (like table, field, trigger ...) - so db stores it inside. Creation is also as simple as executing any other SQL statement.

What I'm really looking for is a way to feed the entire field to a Valentina function/method and have it return just the first or last line, which will be subjected to the regex search.

It's no problem, just slightly modify the UDF above to return not bool but string:

CREATE OR REPLACE FUNCTION GetLastStringListItem( inList VarChar ) RETURNS STRING

BEGIN 

    DECLARE res STRING default ''; 



    DECLARE v_EOL String;

    v_EOL = '

';



    DECLARE items_count LLONG;

    set items_count = List_ItemCount( inList, v_EOL ); 

    IF items_count > 0 THEN

        DECLARE last_item VARCHAR;

    	SET res = List_NthItem( inList, items_count, v_EOL );

    END IF



	RETURN res;

END



--

SELECT GetLastStringListItem(f1) FROM t1;

Very similar UDF for first item - just instead of

List_NthItem( inList, items_count,...)

List_NthItem( inList, 1,...)

--
About calculated fields (methods).

I think you don't need it here, because you may get such field from the cursor instead of having a permanent "method" in the original table:

Cursor cur = db.SqlSelect("SELECT GetLastStringListItem(f1) FROM t1;");

Field f1 = cur.get_field(1);

...

SELECT * FROM t1 WHERE firstNames REGEX 'Jam'

SELECT * FROM t1 WHERE GetFirstStringListItem(f1) REGEX 'Jam'

Thank you Ivan, I learned a lot.

I've blended your approach with Valentina methods, because I understand them better than stored procedures, and I don't know how to create the stored procedures as easily. I create a temp field as follows

For first name search:

if myDatabase.t1.VarCharField("firstName") = nil then 

  dim s as string = "IF(List_ItemCount(names, chr(10))>0, List_NthItem(names, 1, chr(10)), '')"



  call myDatabase.t1.CreateVarCharField("firstName", 120448, EVFlag.fTemporary + EVFlag.fIndexed, s)

end if



select * from t1 where left("firstName", 3) = 'Jam'

Finding the last name is similar except for the method:

if myDatabase.t1.VarCharField("lastName") = nil then

  

 dim s as string = "IF(List_ItemCount(names, chr(10))>0, List_NthItem(names, List_ItemCount(names, chr(10)),      chr(10)), '')"



call myDatabase.t1.CreateVarCharField("lastName", 2044, EVFlag.fTemporary + EVFlag.fIndexed, s)

end if



select * from t1 where left("lastName", 3) = 'Jam'

The temp fields are not retained between launches.