Correction should be the following: SELECT i_pol# FROM pri_ins WHERE 1=(ISALPHA(i_pol#)) + and 1=(ISDIGIT((SGET(i_pol#,1,SLEN(i_pol#)))))
Jim Bentley American Celiac Society [email protected] tel: 1-504-737-3293 ----- Original Message ---- From: James Bentley <[email protected]> To: RBASE-L Mailing List <[email protected]> Sent: Fri, May 14, 2010 1:38:55 PM Subject: [RBASE-L] - RE: Query for character range John, Functions which might be useful: (ISALPHA(value)) Checks the first character of a TEXT string. The function returns a 1 if true and a 0 if false. For example, (ISALPHA('abc')) returns 1 because the first character is a letter. (ISDIGIT(value)) Checks the first character of a TEXT string. The function returns a 1 if true and a 0 if false. For example, (ISDIGIT('abc')) returns 0 because the first character is not a number. SELECT i_pol# FROM pri_ins WHERE 1=(ISALPHA(i_pol#)) + and 1=(ISALPHA((SGET(i_pol#,1,SLEN(i_pol#))))) Jim Bentley American Celiac Society [email protected] tel: 1-504-737-3293 ----- Original Message ---- From: John Croson <[email protected]> To: RBASE-L Mailing List <[email protected]> Sent: Fri, May 14, 2010 1:24:16 PM Subject: [RBASE-L] - RE: Query for character range Thanks, Guys. This really isn't working for me. The data I'm querying on is an insurance policy number, some start with letters, some not. Some end with letters, some not. I'm trying to find those that start with a letter and end with a number. Here are some results from a few tests I've run. This works, and only returns lines that begin with a letter; SELECT i_pol# FROM pri_ins WHERE i_pol# >= 'A%' This doesn't work, and returns nothing even though there are lines that end in a letter, in addition, a number in the place of 'Z' also fails. SELECT i_pol# FROM pri_ins WHERE i_pol# <= '%Z' Obviously BETWEEN won't work since RBase doesn't know how to match lines ending in letters. Am I going to have to resort to cursors, or worse yet, port this to a MS Sql DB?
