Subject Re: [firebird-support] Working with SQL (might be using loop)
Author Vishal Tiwari
Post date 2018-10-07T16:21:53Z
THANKS MARK FOR ALL YOUR KIND SUPPORT AND HELP.

I AM GLAD THAT YOU HELPED.

I HAVE ALSO TRIED TO MAKE ODD CHARACTERS CAPITALIZED. I HAVE ALSO CONSIDERED IF THERE ARE CONSECUTIVE SPACES THEN STILL IT SHOULD WORK.

PLEASE CHECK MY BELOW "SELF-MADE" EXECUTE BLOCK SQL:

SET TERM #;
EXECUTE BLOCK 
RETURNS (MyResult Varchar(1000))
AS
  DECLARE MyText Varchar(100);
  DECLARE TempText Varchar(100);
  DECLARE MYCOUNT INT = 1;
  DECLARE MYPOSITION INT;
  DECLARE SPACEFOUND VARCHAR(5);
  DECLARE DOUPPERCASE VARCHAR(5);
BEGIN
  MyText = 'india     is  my country aLL INDIANS aRe mY bRoThErS';
  SPACEFOUND = 'NO';
  DOUPPERCASE = 'YES';
  MyResult = '';
  TempText = MyText;
  WHILE (MYCOUNT <= CHAR_LENGTH(TempText)) DO
  BEGIN
    MYPOSITION = MYCOUNT;
    --SPACEFOUND = 'NO';
    
    IF (substring(TempText FROM MYCOUNT FOR 1) = ' ')  THEN
    BEGIN        
      MyResult = MyResult || ' ';
      SPACEFOUND = 'YES';
      DOUPPERCASE = 'YES';
    END 
    ELSE
    BEGIN
      IF (DOUPPERCASE = 'YES') THEN 
        MyResult = MyResult || UPPER(substring(TempText FROM MYPOSITION FOR 1));
      ELSE
        MyResult = MyResult || LOWER(substring(TempText FROM MYPOSITION FOR 1));   
        
        
     IF (DOUPPERCASE = 'YES') THEN 
       DOUPPERCASE = 'NO';
     ELSE 
       DOUPPERCASE = 'YES';
    END     
    
    
    MYCOUNT = MYCOUNT + 1;
  END
  SUSPEND;
END#
SET TERM ;#



THANKS A TON FOR YOUR HELP AND SUPPORT AGAIN.

LOOKING FORWARD FOR MEETING YOU AGAIN.

HI SET HOW ARE YOU DEAR?  MISSING YOU ALSO YAAAA....


THANKING YOU AND WITH BEST REGARDS.

VISHAL


On Saturday, 6 October, 2018, 12:32:06 AM GMT-7, setysvar setysvar@... [firebird-support] <firebird-support@yahoogroups.com> wrote:


 

Hi Vishal!

SQL puzzles can be interesting, so I decided to have a go at your quest.

with recursive tmp ( MyInput ) as
( select cast( :InputString as Varchar( 500 ) ) from rdb$database ),
WordLetter ( ThisChar, RestInput, WordPos, CharPos ) as
( select substring( lower( MyInput ) from 1 for 1 ),
         lower( substring( MyInput from 2 for character_length( MyInput) ) ), 1, 1
  from tmp
  union all
  select substring( RestInput from 1 for 1 ),
         substring( RestInput from 2 for character_length( RestInput) ),
         iif ( substring( RestInput from 1 for 1 ) = ' ', WordPos + 1, WordPos ),
         iif ( substring( RestInput from 1 for 1 ) = ' ', 0, CharPos + 1 )
0  from WordLetter
  where trim( RestInput ) > '' ),
Capitalised( FirstLast, Alternate, WordPos, CharPos ) as
( select iif( W1.CharPos = 1 or W2.CharPos is null, upper( W1.ThisChar ), W1.ThisChar ),
         iif( MOD( W1.CharPos, 2 ) = 1, upper( W1.ThisChar ), W1.ThisChar ),
         W1.WordPos, W1.CharPos 
  from WordLetter W1
  left join WordLetter W2 on W1.WordPos = W2.WordPos and W1.CharPos + 1 = W2.CharPos ),
Words( FirstLast, Alternate, WordPos ) as
( select list( FirstLast, '' ), list( Alternate, '' ), WordPos
  from Capitalised
  group by WordPos )
select MyInput, list( FirstLast, ' ' ) as FirstLast, list( Alternate, ' ' ) as Alternate
from Words
cross join tmp
group by MyInput

It worked as I hoped in my test, but admittedly it will not work with two or more subsequent spaces and I do not trust the ordering within LIST, so I'm happy the words and letters weren't garbled.

Set

Den 04.10.2018 10:34, skrev Vishal Tiwari vishualsoft@... [firebird-support]:
Hi,

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.

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...

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..."

SQL for every above points could be sepearte.


Thanks In Advance.


With Best Regards.

Vishal