| **Navigation:**  SQL Drivers >====== Function conversions in Filter Statements ====== | [[general rules for browsing sql based tables.htm|{{btn_prev_n.gif|Previous page}}]][[coming future.htm|{{btn_home_n.gif|Return to chapter overview}}]][[optimizing set processing using the where driver string.htm|{{btn_next_n.gif|Next page}}]] |
|  ||

The following diagram shows in detail the conversion of supported Clarion functions with Clarion 6.3 and Clarion Win32 and prior versions to their SQL equivalents.

{{notebox.jpg|NoteBox.jpg}}

With the new **PROP:ServerCaseInsensitive** set to 0 in version 6.3, the SQL behavior is equivalent to Clarion 6.2

| **Clarion Code:** | **UPPER(var)** |
| 6.3/7 SQL Drivers | Var |
| 6.3/7 Oracle | Var |
| 6.2 SQL Drivers | {fn UCASE(var)} |
| 6.2 Oracle | UPPER(var) |
|  |  |
| **Clarion Code:** | **LOWER(var)** |
| 6.3/7 SQL Drivers | Var |
| 6.3/7 Oracle | Var |
| 6.2 SQL Drivers | {fn LCASE(var)} |
| 6.2 Oracle | LOWER(var) |
|  |  |
| **Clarion Code:** | **NOT var** |
| 6.3/7 SQL Drivers | NOT var |
| 6.3/7 Oracle | NOT var |
| 6.2 SQL Drivers | NOT var |
| 6.2 Oracle | NOT var |
|  |  |
| **Clarion Code:** | **"SUB(var, 1, LEN(var) = constant"** |
| 6.3/7 SQL Drivers | var LIKE 'constant%' |
| 6.3/7 Oracle | var LIKE 'constant%' |
| 6.2 SQL Drivers | var LIKE 'constant%' |
| 6.2 Oracle | var LIKE 'constant%' |
|  |  |
| **Clarion Code:** | **"MATCH(s1, s2, Match:Simple) <;> 0"** |
| 6.3/7 SQL Drivers | s1 = s2 |
| 6.3/7 Oracle | s1 = s3 |
| 6.2 SQL Drivers | s1 = s4 |
| 6.2 Oracle | s1 = s5 |
|  |  |
| **Clarion Code:** | **"MATCH(UPPER(s1), UPPER(s2), Match:Simple) <;> 0"** |
| 6.3/7 SQL Drivers | s1 = s2 |
| 6.3/7 Oracle | s1 = s3 |
| 6.2 SQL Drivers | {fn UCASE(s1)} = {fn UCASE(s2)} |
| 6.2 Oracle | UPPER(s1) = UPPER(s2) |
|  |  |
| **Clarion Code:** | **"MATCH(s1, s2, Match:Wild) <;> 0"** |
| 6.3/7 SQL Drivers | s1 LIKE s2 (with ? Replaced with _ and * with %) |
| 6.3/7 Oracle | s1 LIKE s2 (with ? Replaced with _ and * with %) |
| 6.2 SQL Drivers | s1 LIKE s2 (with ? Replaced with _ and * with %) |
| 6.2 Oracle | s1 LIKE s2 (with ? Replaced with _ and * with %) |
|  |  |
| **Clarion Code:** | **"MATCH(UPPER(s1), UPPER(s2), Match:Wild) <;> 0"** |
| 6.3/7 SQL Drivers | s1 LIKE s2 (with ? Replaced with _ and * with %) |
| 6.3/7 Oracle | s1 LIKE s2 (with ? Replaced with _ and * with %) |
| 6.2 SQL Drivers | {fn UPPER(s1)} LIKE s2 |
|  | (s2 is upper cased and ? replaced with _ and * with %) |
| 6.2 Oracle | UPPER(s1) LIKE s2 |
|  | (s2 is upper cased and ? replaced with _ and * with %) |
|  |  |
| **Clarion Code:** | **"MATCH(s1, s2, Match:Soundex) <;> 0"** |
| 6.3/7 SQL Drivers | {fn SOUNDEX(s1)} = s2 |
| 6.3/7 Oracle | SOUNDEX(s1) = s2 |
| 6.2 SQL Drivers | {fn SOUNDEX(s1)} = s2 |
| 6.2 Oracle | SOUNDEX(s1) = s2 |
|  |  |
| **Clarion Code:** | **"MATCH(UPPER(s1), UPPER(s2), Match:Soundex) <;> 0"** |
| 6.3/7 SQL Drivers | {fn SOUNDEX(s1)} = s2 |
| 6.3/7 Oracle | SOUNDEX(s1) = s2 |
| 6.2 SQL Drivers | {fn SOUNDEX({fn UPPER(s1)})} = {fn UPPER(s2)} |
| 6.2 Oracle | SOUNDEX(UPPER(s1)) = UPPER(s2) |
|  |  |
| **Clarion Code:** | **Constant with non-displayable character** |
| 6.3/7 SQL Drivers | Non-displayable characters are converted to: |
|  | {fn CHAR(ordinal value of character)} |
| 6.3/7 Oracle | Non-displayable characters are converted to: |
|  | CHAR(ordinal value of character) |
| 6.2 SQL Drivers | Non-displayable characters are converted to: |
|  | {fn CHAR(ordinal value of character)} |
| 6.2 Oracle | Non-displayable characters are converted to: |
|  | CHAR(ordinal value of character) |
|  |  |
| **Clarion Code:** | **INSTRING('substring', var, instring(string, var,1,1) <;> 0** |
| **Clarion Code:** | **INSTRING('substring', var, instring(string, var,1) <;> 0** |
| **Clarion Code:** | **INSTRING('substring', var, instring(****//C//****, var) <;> 0** |
|  | (where //C// is a single character string) |
| 6.3/7 SQL Drivers | var LIKE '%string%' |
| 6.3/7 Oracle | var LIKE '%string%' |
| 6.2 SQL Drivers | var LIKE '%string%' |
| 6.2 Oracle | var LIKE '%string%' |
|  |  |
| **Clarion Code:** | **NULL(field) = 0 or <;> 1** |
| 6.3/7 SQL Drivers | field IS NOT NULL |
| 6.3/7 Oracle | field IS NOT NULL |
| 6.2 SQL Drivers | field IS NOT NULL |
| 6.2 Oracle | field IS NOT NULL |
|  |  |
| **Clarion Code:** | **NULL(field) = 1 or <;> 0** |
| 6.3/7 SQL Drivers | field IS NULL |
| 6.3/7 Oracle | field IS NULL |
| 6.2 SQL Drivers | field IS NULL |
| 6.2 Oracle | field IS NULL |
|  |  |
| **Clarion Code:** | date fields |
| 6.3/7 SQL Drivers | {d yyyy-mm-dd} |
| 6.3/7 Oracle | "TO_DATE('dd-mm-yyy', 'DD-MM-YYYY')" |
| 6.2 SQL Drivers | {d yyyy-mm-dd} |
| 6.2 Oracle | "TO_DATE('dd-mm-yyy', 'DD-MM-YYYY')" |
|  |  |
| **Clarion Code:** | time fields |
| 6.3/7 SQL Drivers | {t hh:mm:ss} |
| 6.3/7 Oracle | not supported by Oracle |
| 6.2 SQL Drivers | {t hh:mm:ss} |
| 6.2 Oracle | not supported by Oracle |
|  |  |
| **Clarion Code:** | timestamp fields |
| 6.3/7 SQL Drivers | {ts yyyy-mm-dd hh:mm:ss} |
| 6.3/7 Oracle | "TO_DATE('dd-mm-yyy hh:mm:ss', 'DD-MM-YYYY HH:MI:SS')" |
| 6.2 SQL Drivers | {ts yyyy-mm-dd hh:mm:ss} |
| 6.2 Oracle | "TO_DATE('dd-mm-yyy hh:mm:ss', 'DD-MM-YYYY HH:MI:SS')" |

**See Also:** [[match  return matching values .htm|MATCH]], [[instring  return substring position .htm|INSTRING]], [[null  return null file field .htm|NULL]]