Upshift in SPL

Ok, stored procedures, let's get started. We've got this "find the patient based on what you know" 4gl routine that runs lots of selects. Lookup by birthdate and then compare. Then lookup by ssn, or by name, or hospital number. Great, let's begin with the name query. Well it needs to be upshifted before we can compare it to the searchable form of the name in our database (same problem for soundex or whatever). No problem: let name = upshift(name) Error 201, I don't know what the hell upshift means. Ok, don't panic, this is annoying, but I can write my own stored procedure with the name and functionality of upshift. Let's see, how do I upshift a character. if c >= "a" and c <= "z" then let c = c - 32 barf barf barf No that won't work, but how about let c = ascii(unascii(c)-32). What, no ascii conversion functions? I know, if c = "a" then let c = "A" ... through Z. You got to be kidding!!!! But it works. Almost as ugly and perhaps as slow, I created some quick tables to map letters to and from their ascii values, and upshift and downshift. select up_char into c from updown_chars where down_char = c; I think I'm going to be ill. But let's press on, simply as a research project. I have a crude way to upshift a character, how about a string. No problem: for i = 1 to length(string) let string[i] = upshift(string[i]) What!!!!! No variable subscripts??? You'll swallow string[3], but not string[i]? Nooooooo, the latter isn't not a proper sql expression. So what am I suppose to do? Ok, don't panic. Try shifting instead of indexing. let c = upshift(string[1]); let string = string[2,100]; Yes, that's the ticket, but what to do with c? I know, we'll append it to a new upshifted string as we go. let upstring = upstring || c; Oooops, nothing but blanks. Apparently SQL has all the same obnoxious and offensive habbits of padding strings with blanks and treating this ascii character as some kind of privileged byte. Unfortunately this foolishness is probably written into the SQL standard. No problem, just do this: let upstring = upstring clipped || c; What!! No clipping functions? let upstring = upstring[1,i] || c; Watch it -- no variable subscripts. All right, I'm officially defeated. I was so excited about moving critical sections of code into stored procedures, until I actually tried it. SPL is so restricted and cumbersome that it makes 4gl look like the best language ever conceived. Perhaps I am missing something very obvious, and perhaps the next followup article will put me completely to shame. "You fool, didn't you read manual x page y, where it says let string = sp_upshift(string)?" But if it is there I can't find it. I'd appreciate any help. Has anyone done any kind of character/string manipulation in SPL? How can I upshift a name before comparing against our name table? -- I do more all day -- than most people do before 9 A.M. Karl Dahlke eklhad@siss81.sis.rpslmc.edu (312) 942-8040