Re: Character subscripts in SQL/SPL

This is a multi-part message in MIME format. --------------FD8EC65A2 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Jeff Larsen wrote: > > Since you can't use variables for character subscripts in SQL or > stored procedures, how do you do any useful string manipulation? per the attached. Not fast, but servicable. > > I am trying to write a stored procedure to calculate the soundex > value of a character string. It's going to get ugly if > I can't loop through the characters. Is SQL really this lame? Yes. Yes it is. //////////////// ======================================================= ////////// // Dennis J. Pimple Informix Software, Inc. ////// / /// Principal Consultant 5299 DTC Blvd Suite 740 ///// // //// dennisp@informix.com Englewood CO 80111 //// // ///// /// // ////// recept: 303-850-0210 // // /////// direct: 303-740-5611 Opinions expressed are mine, / /////////// fax: 303-843-6408 and do not necessarily //////////////// http://www.informix.com reflect those of my employer --------------FD8EC65A2 Content-Type: application/futuresplash; name="soundex.spl" Content-Transfer-Encoding: base64 Content-Disposition: inline; filename="soundex.spl" -- there are no ANSI standard for soundex. -- Following are some of the rules, with my way in (parentheses) -- -- Keep the 1st non-vowel letter (I apply the number to the 1st letter -- as well, so words like "Mat" and "Nat" would match). -- -- Skip vowels and other "soft" letters (I do this, skip the letters -- that match to 0 in the chart below). -- -- Skip the 2nd of double letters (I do this, although I think you -- might extend the sense of this to skip the 2nd of double soundex -- matches as well). -- -- Some rules say pad all out to 4 characters with "0", others don't -- (I *do* pad because then you could store the soundex in a smallint -- (since I store the 1st letter as a number), and do something with -- ranges like below) -- -- -- match soundex on only the 1st two values -- -- e.g. if value soundex's to 2234, find all soundexs 2200 to 2266 -- -- all numeric variables below are SMALLINT -- LET x = soundex( value ) -- LET min = x / 100 -- LET min = min * 100 -- LET max = min + 66 -- -- SELECT ... WHERE sdxcolumn BETWEEN min AND max -- -- I'm hardly an expert on soundex, so use/modify at your own risk -- -- -- Apply soundex rules to passed char field -- Soundex maps as follows: -- ABCDEFGHIJKLMNOPQRSTUVWXYZ -- 01230120022455012623010202 -- RETURNS: CHAR(4) soundex code -- -- --DROP PROCEDURE soundex; CREATE PROCEDURE soundex( csound CHAR(255) ) RETURNING CHAR(4); DEFINE l,x SMALLINT; DEFINE cchar CHAR(1); DEFINE lchar CHAR(1); -- last char checked; ignore doubles DEFINE sxcode CHAR(4); LET sxcode = ''; LET l = LENGTH(csound); -- set lchar to something impossible LET lchar = "0"; IF l > 0 THEN FOR x = 1 TO l LET cchar = toupper( getcharat(csound, x) ); IF cchar NOT MATCHES "[ABCDEFGHIJKLMNOPQRSTUVWXYZ]" THEN -- if not in the alphabet, skip it LET lchar = "0"; CONTINUE FOR; END IF IF cchar = lchar THEN -- if this character is the same as the previous one, skip CONTINUE FOR; END IF IF cchar MATCHES "[AEHIOUWY]" THEN -- skip level-0 soundexs CONTINUE FOR; END IF LET lchar = cchar; IF lchar MATCHES "[AEHIOUWY]" THEN -- for completeness only; we trap these up above LET sxcode = TRIM(sxcode) || "0"; ELIF lchar MATCHES "[BFPV]" THEN LET sxcode = TRIM(sxcode) || "1"; ELIF lchar MATCHES "[CGJKQSXZ]" THEN LET sxcode = TRIM(sxcode) || "2"; ELIF lchar MATCHES "[DT]" THEN LET sxcode = TRIM(sxcode) || "3"; ELIF lchar = "L" THEN LET sxcode = TRIM(sxcode) || "4"; ELIF lchar MATCHES "[MN]" THEN LET sxcode = TRIM(sxcode) || "5"; ELIF lchar = "R" THEN LET sxcode = TRIM(sxcode) || "6"; END IF IF LENGTH(sxcode) = 4 THEN EXIT FOR; END IF END FOR -- pad out to 0's WHILE LENGTH(sxcode) < 4 LET sxcode = TRIM(sxcode) || "0"; END WHILE -- LENGTH(sxcode) < 4 END IF RETURN sxcode; END PROCEDURE; UPDATE STATISTICS FOR PROCEDURE soundex; -- getcharat -- Take the string and return the character at position pos -- It exists because variable subscripting (e.g. variable[x,y]) -- doesn't work in SPL --DROP PROCEDURE getcharat; CREATE PROCEDURE getcharat(str CHAR(255), pos INTEGER) RETURNING CHAR(1); DEFINE i INTEGER; IF pos > 1 THEN -- loop through and chop off the first letter of the string -- until we get to the one we want. FOR i = 2 TO pos LET str = str[2,255]; END FOR; END IF RETURN str[1,1]; END PROCEDURE; UPDATE STATISTICS FOR PROCEDURE getcharat; -- take a lowercase letter and return its uppercase equivalent, -- otherwise return what was sent -- there might be some performance enhancement by ordering the letters -- in order of common usage, but I doubt if it's much --DROP PROCEDURE toupper; CREATE PROCEDURE toupper(fromchar CHAR(1)) RETURNING CHAR(1); IF fromchar = 'a' THEN RETURN 'A'; ELIF fromchar = 'b' THEN RETURN 'B'; ELIF fromchar = 'c' THEN RETURN 'C'; ELIF fromchar = 'd' THEN RETURN 'D'; EL