Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 9 of 9 total
Thread INITCAP Function
Thu, Mar 12 2009 6:34 PMPermanent Link

Lance Rasmussen

Jazzie Software

Avatar

Team Elevate Team Elevate

Tim,

If I missed the SQL funtion in EDB's SQL Manual, my appologies.   I'm
looking for equivelant string function in EDB that Oracle has that I had to
teach.  INITCAP which simply forces everything to be lower case and
uppercases the first character in each word.

It may be a function that is concentric to each DB, but would be a nice
thing for EDB.

Lance


__________ Information from ESET NOD32 Antivirus, version of virus signature database 3931 (20090312) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com


Fri, Mar 13 2009 1:00 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Lance,

<< If I missed the SQL funtion in EDB's SQL Manual, my appologies.   I'm
looking for equivelant string function in EDB that Oracle has that I had to
teach.  INITCAP which simply forces everything to be lower case and
uppercases the first character in each word.

It may be a function that is concentric to each DB, but would be a nice
thing for EDB. >>

I'll add it to the list, but in the meantime you can use this:

CREATE FUNCTION INITCAP(IN Value VARCHAR)
RETURNS VARCHAR
BEGIN
  DECLARE CurPos INTEGER DEFAULT 1;
  DECLARE NextPos INTEGER DEFAULT 0;
  DECLARE ValueLen INTEGER DEFAULT LENGTH(Value);
  DECLARE CurChar CHAR(1) DEFAULT ' ';
  DECLARE UpperChar BOOLEAN DEFAULT TRUE;
  DECLARE Result VARCHAR DEFAULT '';

  IF ValueLen > 0 THEN

     WHILE CurPos <= ValueLen DO

        SET CurChar = SUBSTRING(Value, CurPos, 1);

        IF CurChar <> ' ' THEN
           IF UpperChar THEN
              SET Result = Result + UPPER(CurChar);
              SET UpperChar = False;
           ELSE
              SET Result = Result + LOWER(CurChar);
           END IF;
        ELSE
           SET Result = Result + CurChar;
           SET UpperChar = True;
        END IF;

        SET CurPos = CurPos + 1;

     END WHILE;

  END IF;

  RETURN Result;

END

--
Tim Young
Elevate Software
www.elevatesoft.com

Fri, Mar 13 2009 3:13 PMPermanent Link

Lance Rasmussen

Jazzie Software

Avatar

Team Elevate Team Elevate

Ahh..  Nice...   Thanks. This will work well in the mean time.

Lance


"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:B3B9B7E1-3EFE-49CB-94E5-BCAAAD0613F9@news.elevatesoft.com...
> Lance,
>
> << If I missed the SQL funtion in EDB's SQL Manual, my appologies.   I'm
> looking for equivelant string function in EDB that Oracle has that I had
> to teach.  INITCAP which simply forces everything to be lower case and
> uppercases the first character in each word.
>
> It may be a function that is concentric to each DB, but would be a nice
> thing for EDB. >>
>
> I'll add it to the list, but in the meantime you can use this:
>
> CREATE FUNCTION INITCAP(IN Value VARCHAR)
> RETURNS VARCHAR
> BEGIN
>   DECLARE CurPos INTEGER DEFAULT 1;
>   DECLARE NextPos INTEGER DEFAULT 0;
>   DECLARE ValueLen INTEGER DEFAULT LENGTH(Value);
>   DECLARE CurChar CHAR(1) DEFAULT ' ';
>   DECLARE UpperChar BOOLEAN DEFAULT TRUE;
>   DECLARE Result VARCHAR DEFAULT '';
>
>   IF ValueLen > 0 THEN
>
>      WHILE CurPos <= ValueLen DO
>
>         SET CurChar = SUBSTRING(Value, CurPos, 1);
>
>         IF CurChar <> ' ' THEN
>            IF UpperChar THEN
>               SET Result = Result + UPPER(CurChar);
>               SET UpperChar = False;
>            ELSE
>               SET Result = Result + LOWER(CurChar);
>            END IF;
>         ELSE
>            SET Result = Result + CurChar;
>            SET UpperChar = True;
>         END IF;
>
>         SET CurPos = CurPos + 1;
>
>      END WHILE;
>
>   END IF;
>
>   RETURN Result;
>
> END
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>
>
> __________ Information from ESET NOD32 Antivirus, version of virus
> signature database 3935 (20090313) __________
>
> The message was checked by ESET NOD32 Antivirus.
>
> http://www.eset.com
>
>
>


__________ Information from ESET NOD32 Antivirus, version of virus signature database 3935 (20090313) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com


Fri, Mar 13 2009 4:56 PMPermanent Link

Lance Rasmussen

Jazzie Software

Avatar

Team Elevate Team Elevate

For grins and some improvements, although I don't think within the spec of
how Oracle treats this function and could be another built-in function or
just a code FYI, it may be handy for others taking your function and
extending it to work more specific with a persons name and factor the
O'Irish names, the McScottish names and the
Maried-Several-Times-And-I-Like-A-Lot-Of-Initials-On-My-Towels hyphenates.

Lance



CREATE FUNCTION INITCAPSPERSONNAME(IN Value VARCHAR)
RETURNS VARCHAR
BEGIN
  DECLARE CurPos INTEGER DEFAULT 1;
  DECLARE NextPos INTEGER DEFAULT 0;
  DECLARE ValueLen INTEGER DEFAULT LENGTH(Value);
  DECLARE CurChar CHAR(1) DEFAULT ' ';
  DECLARE UpperChar BOOLEAN DEFAULT TRUE;
  DECLARE MCChar BOOLEAN DEFAULT FALSE;
  DECLARE OChar BOOLEAN DEFAULT FALSE;
  DECLARE Result VARCHAR DEFAULT '';

  IF ValueLen > 0 THEN

     WHILE CurPos <= ValueLen DO

        SET CurChar = SUBSTRING(Value, CurPos, 1);

        IF CurChar <> ' ' THEN
           IF UpperChar THEN
              SET Result = Result + UPPER(CurChar);
              SET UpperChar = False;
           ELSE
              SET Result = Result + LOWER(CurChar);
           END IF;
        ELSE
           SET Result = Result + CurChar;
           SET UpperChar = True;
        END IF;

     IF CurPos = 1 AND CurChar='M' THEN
       SET MCChar=TRUE;
     END IF;

     IF CurPos = 2 AND MCChar = TRUE AND UPPER(CurChar)='C' THEN
       SET UpperChar=TRUE;
     END IF;

     IF CurPos = 1 AND CurChar='O' THEN
       SET OChar=TRUE;
     END IF;

     IF CurPos = 2 AND OChar = TRUE AND CurChar='''' THEN
       SET UpperChar=TRUE;
     END IF;
        SET CurPos = CurPos + 1;

     END WHILE;

  END IF;

  RETURN Result;

END



"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:B3B9B7E1-3EFE-49CB-94E5-BCAAAD0613F9@news.elevatesoft.com...
> Lance,
>
> << If I missed the SQL funtion in EDB's SQL Manual, my appologies.   I'm
> looking for equivelant string function in EDB that Oracle has that I had
> to teach.  INITCAP which simply forces everything to be lower case and
> uppercases the first character in each word.
>
> It may be a function that is concentric to each DB, but would be a nice
> thing for EDB. >>
>
> I'll add it to the list, but in the meantime you can use this:
>
> CREATE FUNCTION INITCAP(IN Value VARCHAR)
> RETURNS VARCHAR
> BEGIN
>   DECLARE CurPos INTEGER DEFAULT 1;
>   DECLARE NextPos INTEGER DEFAULT 0;
>   DECLARE ValueLen INTEGER DEFAULT LENGTH(Value);
>   DECLARE CurChar CHAR(1) DEFAULT ' ';
>   DECLARE UpperChar BOOLEAN DEFAULT TRUE;
>   DECLARE Result VARCHAR DEFAULT '';
>
>   IF ValueLen > 0 THEN
>
>      WHILE CurPos <= ValueLen DO
>
>         SET CurChar = SUBSTRING(Value, CurPos, 1);
>
>         IF CurChar <> ' ' THEN
>            IF UpperChar THEN
>               SET Result = Result + UPPER(CurChar);
>               SET UpperChar = False;
>            ELSE
>               SET Result = Result + LOWER(CurChar);
>            END IF;
>         ELSE
>            SET Result = Result + CurChar;
>            SET UpperChar = True;
>         END IF;
>
>         SET CurPos = CurPos + 1;
>
>      END WHILE;
>
>   END IF;
>
>   RETURN Result;
>
> END
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>
>
> __________ Information from ESET NOD32 Antivirus, version of virus
> signature database 3935 (20090313) __________
>
> The message was checked by ESET NOD32 Antivirus.
>
> http://www.eset.com
>
>
>


__________ Information from ESET NOD32 Antivirus, version of virus signature database 3935 (20090313) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com


Fri, Mar 13 2009 9:12 PMPermanent Link

Lance Rasmussen

Jazzie Software

Avatar

Team Elevate Team Elevate

Minor Corrections:

BEGIN
 DECLARE CurPos INTEGER DEFAULT 1;
 DECLARE NextPos INTEGER DEFAULT 0;
 DECLARE ValueLen INTEGER DEFAULT LENGTH(Value);
 DECLARE CurChar CHAR(1) DEFAULT ' ';
 DECLARE UpperChar BOOLEAN DEFAULT TRUE;
 DECLARE MCChar BOOLEAN DEFAULT TRUE;
 DECLARE OChar BOOLEAN DEFAULT TRUE;
 DECLARE Result VARCHAR DEFAULT '';

 IF ValueLen > 0 THEN

   WHILE CurPos <= ValueLen DO

     SET CurChar = SUBSTRING(Value, CurPos, 1);

     IF CurChar <> ' ' THEN
       IF UpperChar THEN
         SET Result = Result + UPPER(CurChar);
         SET UpperChar = False;
       ELSE
         SET Result = Result + LOWER(CurChar);
       END IF;
     ELSE
       SET Result = Result + CurChar;
       SET UpperChar = True;
     END IF;

     IF CurPos = 1 AND UPPER(CurChar)='M' THEN
       SET MCChar=TRUE;
     ELSE
       SET MCChar=FALSE;
     END IF;

     IF CurPos = 2 AND MCChar = TRUE AND UPPER(CurChar)='C' THEN
       SET UpperChar=TRUE;
       SET MCChar=FALSE;
     END IF;

     IF CurPos = 1 AND UPPER(CurChar)='O' THEN
       SET OChar=TRUE;
     ELSE
       SET OCHar=FALSE;
     END IF;

     IF CurPos = 2 AND OChar = TRUE AND CurChar='''' THEN
       SET UpperChar=TRUE;
       SET OChar=FALSE;
     END IF;

     IF CurChar = '-' THEN
       SET UpperChar=TRUE;
     END IF;

     SET CurPos = CurPos + 1;

   END WHILE;

 END IF;

 RETURN Result;

END


"Lance Rasmussen [Team Elevate]" <lance@jazziesoftware.com> wrote in message
news:935BD2E2-4C0B-40D6-A055-F90E9304181B@news.elevatesoft.com...
> For grins and some improvements, although I don't think within the spec of
> how Oracle treats this function and could be another built-in function or
> just a code FYI, it may be handy for others taking your function and
> extending it to work more specific with a persons name and factor the
> O'Irish names, the McScottish names and the
> Maried-Several-Times-And-I-Like-A-Lot-Of-Initials-On-My-Towels hyphenates.
>
> Lance
>
>
>
> CREATE FUNCTION INITCAPSPERSONNAME(IN Value VARCHAR)
> RETURNS VARCHAR
> BEGIN
>   DECLARE CurPos INTEGER DEFAULT 1;
>   DECLARE NextPos INTEGER DEFAULT 0;
>   DECLARE ValueLen INTEGER DEFAULT LENGTH(Value);
>   DECLARE CurChar CHAR(1) DEFAULT ' ';
>   DECLARE UpperChar BOOLEAN DEFAULT TRUE;
>   DECLARE MCChar BOOLEAN DEFAULT FALSE;
>   DECLARE OChar BOOLEAN DEFAULT FALSE;
>   DECLARE Result VARCHAR DEFAULT '';
>
>   IF ValueLen > 0 THEN
>
>      WHILE CurPos <= ValueLen DO
>
>         SET CurChar = SUBSTRING(Value, CurPos, 1);
>
>         IF CurChar <> ' ' THEN
>            IF UpperChar THEN
>               SET Result = Result + UPPER(CurChar);
>               SET UpperChar = False;
>            ELSE
>               SET Result = Result + LOWER(CurChar);
>            END IF;
>         ELSE
>            SET Result = Result + CurChar;
>            SET UpperChar = True;
>         END IF;
>
>      IF CurPos = 1 AND CurChar='M' THEN
>        SET MCChar=TRUE;
>      END IF;
>
>      IF CurPos = 2 AND MCChar = TRUE AND UPPER(CurChar)='C' THEN
>        SET UpperChar=TRUE;
>      END IF;
>
>      IF CurPos = 1 AND CurChar='O' THEN
>        SET OChar=TRUE;
>      END IF;
>
>      IF CurPos = 2 AND OChar = TRUE AND CurChar='''' THEN
>        SET UpperChar=TRUE;
>      END IF;
>         SET CurPos = CurPos + 1;
>
>      END WHILE;
>
>   END IF;
>
>   RETURN Result;
>
> END
>
>
>
> "Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
> news:B3B9B7E1-3EFE-49CB-94E5-BCAAAD0613F9@news.elevatesoft.com...
>> Lance,
>>
>> << If I missed the SQL funtion in EDB's SQL Manual, my appologies.   I'm
>> looking for equivelant string function in EDB that Oracle has that I had
>> to teach.  INITCAP which simply forces everything to be lower case and
>> uppercases the first character in each word.
>>
>> It may be a function that is concentric to each DB, but would be a nice
>> thing for EDB. >>
>>
>> I'll add it to the list, but in the meantime you can use this:
>>
>> CREATE FUNCTION INITCAP(IN Value VARCHAR)
>> RETURNS VARCHAR
>> BEGIN
>>   DECLARE CurPos INTEGER DEFAULT 1;
>>   DECLARE NextPos INTEGER DEFAULT 0;
>>   DECLARE ValueLen INTEGER DEFAULT LENGTH(Value);
>>   DECLARE CurChar CHAR(1) DEFAULT ' ';
>>   DECLARE UpperChar BOOLEAN DEFAULT TRUE;
>>   DECLARE Result VARCHAR DEFAULT '';
>>
>>   IF ValueLen > 0 THEN
>>
>>      WHILE CurPos <= ValueLen DO
>>
>>         SET CurChar = SUBSTRING(Value, CurPos, 1);
>>
>>         IF CurChar <> ' ' THEN
>>            IF UpperChar THEN
>>               SET Result = Result + UPPER(CurChar);
>>               SET UpperChar = False;
>>            ELSE
>>               SET Result = Result + LOWER(CurChar);
>>            END IF;
>>         ELSE
>>            SET Result = Result + CurChar;
>>            SET UpperChar = True;
>>         END IF;
>>
>>         SET CurPos = CurPos + 1;
>>
>>      END WHILE;
>>
>>   END IF;
>>
>>   RETURN Result;
>>
>> END
>>
>> --
>> Tim Young
>> Elevate Software
>> www.elevatesoft.com
>>
>>
>> __________ Information from ESET NOD32 Antivirus, version of virus
>> signature database 3935 (20090313) __________
>>
>> The message was checked by ESET NOD32 Antivirus.
>>
>> http://www.eset.com
>>
>>
>>
>
>
> __________ Information from ESET NOD32 Antivirus, version of virus
> signature database 3935 (20090313) __________
>
> The message was checked by ESET NOD32 Antivirus.
>
> http://www.eset.com
>
>
>
>
> __________ Information from ESET NOD32 Antivirus, version of virus
> signature database 3936 (20090313) __________
>
> The message was checked by ESET NOD32 Antivirus.
>
> http://www.eset.com
>
>
>


__________ Information from ESET NOD32 Antivirus, version of virus signature database 3936 (20090313) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com


Fri, Mar 13 2009 10:13 PMPermanent Link

"Jeff Cook"
Lance Rasmussen [Team Elevate] wrote:

> For grins and some improvements, although I don't think within the
> spec of how Oracle treats this function and could be another built-in
> function or just a code FYI, it may be handy for others taking your
> function and extending it to work more specific with a persons name
> and factor the O'Irish names, the McScottish names and the
> Maried-Several-Times-And-I-Like-A-Lot-Of-Initials-On-My-Towels
> hyphenates.
>

Reminds me of the ProperName function in LotusScript ... though I
haven't looked at that in 15 years!


--
Jeff Cook
Aspect Systems Ltd
www.aspect.co.nz
+
Joan and Jeff Cook
The Cooks Oasis
www.cookislandsoasis.com
Sat, Mar 14 2009 5:16 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Jeff

It also reminds me of writing a fleet management system when I was at Imperial Foods. I gave up in the end - I valued my sanity to much. It seemed that every time I thought I'd got it sorted someone else joined with another variant.


Roy Lambert
Sat, Mar 14 2009 5:26 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Lance


Am I mistaken or have you forgotten the MacScottishName? And then there are the one's who have dropped the ' but kept the capital following the O plus the ones that for some reason don't have a capital following the ' not to forget the scots who don't have a capital following the Mac, Mc or MC (rare but I encountered them).

My personal opinion is that there are to many exceptions to have a sane rule. My favourite was I built in a rule saying surnames had to be at least three characters - then we had our first Chinese employee allocated a car.....

Roy Lambert
Sun, Mar 15 2009 2:41 AMPermanent Link

Lance Rasmussen

Jazzie Software

Avatar

Team Elevate Team Elevate

LMAO Roy....   yeah there are a ton of variants, for sure.   It's a no win,
for sure.


"Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message
news:86B80AE0-FEDF-4BC0-970C-6FB36BED7E3D@news.elevatesoft.com...
> Lance
>
>
> Am I mistaken or have you forgotten the MacScottishName? And then there
> are the one's who have dropped the ' but kept the capital following the O
> plus the ones that for some reason don't have a capital following the '
> not to forget the scots who don't have a capital following the Mac, Mc or
> MC (rare but I encountered them).
>
> My personal opinion is that there are to many exceptions to have a sane
> rule. My favourite was I built in a rule saying surnames had to be at
> least three characters - then we had our first Chinese employee allocated
> a car.....
>
> Roy Lambert
>
>
> __________ Information from ESET NOD32 Antivirus, version of virus
> signature database 3937 (20090314) __________
>
> The message was checked by ESET NOD32 Antivirus.
>
> http://www.eset.com
>
>
>


__________ Information from ESET NOD32 Antivirus, version of virus signature database 3937 (20090314) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com


Image