| Subject | Re: [firebird-support] Working with SQL (might be using loop) |
|---|---|
| Author | setysvar |
| Post date | 2018-10-06T07:31:33Z |
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]:
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