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'
-- First item:
'^([^\\n]*\\n){0}your_value'
-- Second item:
'^([^\\n]*\\n){1}your_value'
...
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;
But I don't see where to make created procudures, how to create them in Xojo, and where to store them.
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.
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;
List_NthItem( inList, items_count,...)
List_NthItem( inList, 1,...)
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'
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'
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'