On 4-10-2018 10:34, Vishal Tiwari vishuals...@yahoo.co.in [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