| Subject | Re: [firebird-support] Is there a way of doubling internal quotes in a varchar without UDFs? |
|---|---|
| Author | Helen Borrie |
| Post date | 2004-12-11T00:54:18Z |
At 09:53 PM 10/12/2004 +0000, you wrote:
exception? Or, if parsing the string for some purpose, can you explain a
bit more about what you are trying to achieve?
The string presumably comes by either receiving it as an input parameter or
by selecting it from stored data. Either way, by the time it is accepted
into the procedure, the apostrophe is already stored as a literal character.
Here's a trivial testcase script that illustrates existing behaviour:
create table apostrophes (
data1 varchar(20),
data2 varchar(40));
commit;
insert into apostrophes (data1)
values ('O''Flaherty''s');
insert into apostrophes (data1)
values ('They didn''t practise'); /* 20 characters + escape character */
commit;
set term ^;
create procedure apotest (invar varchar(20))
as
declare astring varchar(40);
begin
for select data1 from apostrophes
into :astring do
begin
update apostrophes set data2 = :astring || :invar
where data1 = :astring; /* search clause with apostrophes */
end
end ^
commit^
set term ;^
execute procedure apotest(' rabbits'' habits');
./heLen
simply tells the parser that the other one is a literal character, not a
string delimiter. From here on, the apostrophe is just an ordinary character.
I could imagine some kind of parsing operation where you were dissecting
varchars and reassembling them into a new string, where it might be
necessary to trap the apostrophe character and give it some special
treatment - is this what you need to do? That requirement, however, seems
incompatible with your aversion to UDFs....
./heLen
>Hi,Is this a guess, or do you have an example where there is an
>
>I have a little problem that I can't seem to figure out the answer
>to.
>
>In a PSQL module I need to quote a string, but if there is a quote
>inside of it, it will be the first (wrong * ) result shown below,
>whereas I would need the second one.
>
>* 'This isn't it'
> 'This isn''t it'.
exception? Or, if parsing the string for some purpose, can you explain a
bit more about what you are trying to achieve?
The string presumably comes by either receiving it as an input parameter or
by selecting it from stored data. Either way, by the time it is accepted
into the procedure, the apostrophe is already stored as a literal character.
Here's a trivial testcase script that illustrates existing behaviour:
create table apostrophes (
data1 varchar(20),
data2 varchar(40));
commit;
insert into apostrophes (data1)
values ('O''Flaherty''s');
insert into apostrophes (data1)
values ('They didn''t practise'); /* 20 characters + escape character */
commit;
set term ^;
create procedure apotest (invar varchar(20))
as
declare astring varchar(40);
begin
for select data1 from apostrophes
into :astring do
begin
update apostrophes set data2 = :astring || :invar
where data1 = :astring; /* search clause with apostrophes */
end
end ^
commit^
set term ;^
execute procedure apotest(' rabbits'' habits');
./heLen
>Right now I am adding the quotes this way:Doubling is an input thingy - one apostrophe is an escape character - it
>
>''''||FIELDVALUE||'''' but that of course leaves the inside non-
>doubled which is wrong.
simply tells the parser that the other one is a literal character, not a
string delimiter. From here on, the apostrophe is just an ordinary character.
I could imagine some kind of parsing operation where you were dissecting
varchars and reassembling them into a new string, where it might be
necessary to trap the apostrophe character and give it some special
treatment - is this what you need to do? That requirement, however, seems
incompatible with your aversion to UDFs....
./heLen