On 4-10-2018 10:34, Vishal Tiwari vishualsoft@...
[firebird-support] wrote:

> I am trying to get below stuff using SQL only.
>
> 1. If I have any statement like "World is good enough to enjoy..." then
> in I need to get SQL out put as
> "WorlD IS GooD EnougH TO EnjoY..." that is first and last character of
> each word should be capital letter and rest should be in small letters.

These things are generally easier (and more efficient) to do in 'normal'
programming languages. Consider your options carefully before resorting
to doing this in SQL.

You probably can't do this with only SQL (or at least, I can't think of
an option that isn't at least extremely painful to write and hard to
understand, and I'm not sure that approach will even work).

Instead, you will need to use a Firebird 3 function (or a (selectable)
stored procedure, or a native UDF or UDR) to do this.

With a PSQL function, you'd be able to do something like:

create function capitalize_first_last_of_word(inputval varchar(100))
returns varchar(100)
as
declare outputval varchar(100) = '';
declare previousChar char(1) = ' ';
declare currentChar char(1);
declare nextChar char(1);
declare nextPosition integer;
declare stringLength integer;

declare function charAt(string varchar(100), charPos integer)
returns char(1)
as
begin
return substring(string from charPos for 1);
end
begin
stringLength = char_length(inputval);
if (stringLength is null or stringLength = 0) then
begin
-- preserve null or empty string
return inputval;
end
currentChar = charAt(inputVal, 1);
nextPosition = 2;

while (nextPosition <= stringLength) do
begin
nextChar = charAt(inputVal, nextposition);
outputVal = outputVal || case
when previousChar = ' ' or nextChar in (' ', '.') then
upper(currentChar)
else lower(currentChar)
end;

previousChar = currentChar;
currentChar = nextChar;
nextPosition = nextPosition + 1;
end

outputVal = outputVal || upper(currentChar);

return outputVal;
end

> 2. If I give any number like 007, 10002, 5645 then if the first digit of
> the integer value is 0 or 1 then a minus sign should be prefixed and if
> it is not 0 or 1 then it should prefix + sign. We can enter integer
> value in string format as well if needed. like -007, -10002, +5645...

Integers don't have prefixed zeroes, so these need to be strings.
Ignoring cases where you are passed a non-integer value, you can do
something like:

case
when left(numbervalue, 1) in ('0', '1') then '-'
else '+'
end || numbervalue

> 3. Also, I need to make odd position character in capital letter in a
> given statement like  "World is good enough to enjoy..." should be
> output as  "WoRlD Is GoOd EnUuGh To EnJoY..."

Similar as point 1, this would be pretty hard to do with pure SQL.

With a function, you can do something like:

create function capitalize_odd(inputval varchar(100))
returns varchar(100)
as
declare outputval varchar(100) = '';
declare currentChar char(1);
declare currentPosition integer = 1;
declare stringLength integer;

declare function charAt(string varchar(100), charPos integer) returns
char(1)
as
begin
return substring(string from charPos for 1);
end
begin
stringLength = char_length(inputval);
if (stringLength is null or stringLength = 0) then
begin
-- preserve null or empty string
return inputval;
end

while (currentPosition <= stringLength) do
begin
currentChar = charAt(inputval, currentPosition);
outputVal = outputVal || case mod(currentPosition, 2)
when 1 then upper(currentChar)
else lower(currentChar)
end;

currentPosition = currentPosition + 1;
end
return outputVal;
end

Note: I have not exhaustively tested these.

--
Mark Rotteveel