Commented by: @asfernandes

That seems as a completelly different thing than SQL strings, which are immutable.

I don't remember any DBMS doing that too.

Commented by: @livius2

I do not know what SQL string is. I am talking about PSQL variable of type VARCHAR or CHAR.
I need to have simple access to any char in this varable as i have in "any" programing language.

Without this, writing string function operated on chars in PSQL is not efficient at all.

Try write efficient function to generate e.g. camel style string from "string" parameter.
If you do this by substring + concat in the loop, you have then very ineficient code.

Commented by: @asfernandes

> to any char in this varable as i have in "any" programing language

Show me examples in PLSQL, T-SQL, PG/PL SQL, please.

You don't have S[N] = S in any programming language that have immutable string.

You don't have even read S[N] in Java.

Commented by: @asfernandes

For SUBSTRING + concat + SUBSTRING, use OVERLAY function.

Commented by: @livius2

This is e.g. slow compared to udf equivalent

SET TERM ^ ;
CREATE FUNCTION UP_FIRST_STR(S VARCHAR(100))
RETURNS VARCHAR(100)
AS
DECLARE VARIABLE VAR_I INTEGER;
DECLARE VARIABLE VAR_LEN INTEGER;
DECLARE VARIABLE VAR_RESULT VARCHAR(100);
DECLARE VARIABLE VAR_C1 CHAR(1);
DECLARE VARIABLE VAR_C2 CHAR(1);
BEGIN
if (S IS NULL) then
RETURN S;
VAR_LEN = char_length(S);
VAR_I = 1;
VAR_RESULT = '';
VAR_C1 = ' ';
WHILE (VAR_I<=VAR_LEN) DO
begin
VAR_C2 = SUBSTRING(:S FROM :VAR_I FOR 1);
IF (VAR_C1 IN (' ', '_')) THEN
VAR_RESULT = :VAR_RESULT || UPPER(:VAR_C2); else
VAR_RESULT = :VAR_RESULT || LOWER(:VAR_C2);
VAR_I = VAR_I + 1;
VAR_C1 = VAR_C2;
END
RETURN VAR_RESULT;
END^
SET TERM ; ^

SELECT UP_FIRST_STR('Lorem ipsum dolor sit amet') FROM RDB$DATABASE

---------------------------------------------------------------------------------------------

i need to be possible something like this

SET TERM ^ ;
CREATE FUNCTION UP_FIRST_STR2(S VARCHAR(100))
RETURNS VARCHAR(100)
AS
DECLARE VARIABLE VAR_I INTEGER;
DECLARE VARIABLE VAR_LEN INTEGER;
DECLARE VARIABLE VAR_RESULT VARCHAR(100);
DECLARE VARIABLE VAR_C1 CHAR(1);
DECLARE VARIABLE VAR_C2 CHAR(1);
BEGIN
if (S IS NULL) then
RETURN S;
VAR_LEN = char_length(S);
VAR_I = 1;
VAR_RESULT = S;
VAR_C1 = ' ';
WHILE (VAR_I<=VAR_LEN) DO
begin
VAR_C2 = VAR_RESULT[:VAR_I];
IF (VAR_C1 IN (' ', '_')) THEN
VAR_RESULT[:VAR_I] = UPPER(:VAR_C2); else
VAR_RESULT[:VAR_I] = LOWER(:VAR_C2);
VAR_I = VAR_I + 1;
VAR_C1 = VAR_C2;
END
RETURN VAR_RESULT;
END^
SET TERM ; ^

maybe will be also possible to remove the need to have VAR_RESULT at all
and operate on "S" directly.

-------------------------------------------

>> You don't have S[N] = S in any programming language that have immutable string.
becaouse of this i do not use such langages

>> You don't have even read S[N] in Java.
and this is big minus for it

Commented by: Sean Leyne (seanleyne)

Karol,

So you are asking for "new built-in string manipulation, by position, function"?

Commented by: @livius2

Yes, efficient and simple to use string manipulation without need to new allocation of string and need to use substring at all. Same as is in Delphi.

P.s.
I see that in FB4 many udf's was replaced by psql but do you compare speed?
This request is because i also need to change many custom udf to psql but when i run speed test than i see it is not possible and i still must use udf/udr.

Commented by: Sean Leyne (seanleyne)

Edit to the ticket summary, was "Add simple indexed(positioned) operations on strings (varchar, char) in Triggers, Procedures, functions"

Commented by: Sean Leyne (seanleyne)

Performance comparison of new built-in functions/psql vs. UDF should be discussed on support/devel mailing lists, not here.

Commented by: @asfernandes

Mutable strings are a major architecture change that should be discussed in devel list, and I doubt it would be implemented, so closing ticket.

And SUBSTRING is already the standard function to read a string portion.

Commented by: Sean Leyne (seanleyne)

I do not agree that this ticket should be closed. At least, not without larger discussion. There are plenty of tickets for features/improvements that are of questionable value/implementation, that doesn't mean they should be closed.

Commented by: @asfernandes

Then reopen it. "There are plenty of tickets" that the developer feel it's inappropriate and close it (as I did here), and "there are plenty of tickets" that will never be done and are forever open until someone found and does a cleanup. That second group, unfortunately, seems for me much larger than the first.