Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM Enhancement Requests and Suggestions » View Thread |
Messages 1 to 9 of 9 total |
INITCAP Function |
Thu, Mar 12 2009 6:34 PM | Permanent Link |
Lance Rasmussen Jazzie Software 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 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. 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 PM | Permanent Link |
Lance Rasmussen Jazzie Software 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 PM | Permanent Link |
Lance Rasmussen Jazzie Software 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 PM | Permanent Link |
Lance Rasmussen Jazzie Software 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 PM | Permanent 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 AM | Permanent Link |
Roy Lambert NLH Associates 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 AM | Permanent Link |
Roy Lambert NLH Associates 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 AM | Permanent Link |
Lance Rasmussen Jazzie Software 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 |
This web page was last updated on Friday, March 29, 2024 at 03:30 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |