---
title: "标量函数转义功能 - ODBC 驱动程序  V2.0.8 | OceanBase 文档中心"
description: "标量函数转义功能 OceanBase Connector/ODBC 在 Oracle 模式下，支持对 IFNULL 、 SUBSTRING 、 LEFT 三个标量函数的转义功能。 IFNULL 函数 IFNULL 函数的语法如下： {fn IFNULL(exp1, exp2) } IFNULL 函数先判断 exp1 …"
image: https://mdn.alipayobjects.com/huamei_22khvb/afts/img/A*OSPzQ6GUQF4AAAAAQHAAAAgAeiGDAQ/original
---
切换语言

- 中文站 - 简体中文
- International - English
- 日本站 - 日本語

文档反馈![](https://mdn.alipayobjects.com/huamei_22khvb/afts/img/A*0V2BT7xsMSQAAAAAAAAAAAAADiGDAQ/original) ODBC 驱动程序 V 2.0.8

# 标量函数转义功能

更新时间：2026-04-09 15:10:59

[编辑](https://github.com/oceanbase/connector-odbc-doc/edit/V2.0.8/zh-CN/300.user-guide/400.scalar_function.md)  

OceanBase Connector/ODBC 在 Oracle 模式下，支持对 `IFNULL`、`SUBSTRING`、`LEFT` 三个标量函数的转义功能。

## IFNULL 函数

`IFNULL` 函数的语法如下：

```sql
{fn IFNULL(exp1, exp2) }

```

`IFNULL` 函数先判断 `exp1` 是否为 `NULL`，如果不为`NULL` 则返回 `exp1`, 否则返回 `exp2`。

如下示例中 `exp1` 为 `NULL`，则结果返回 `exp2`，即 “aaa”。

```sql
SELECT {fn IFNULL(NULL, 'aaa')} FROM DUAL;

```

对于如下示例，如果 `table.a` 为 `NULL`，则结果为 `b`；如果 `table.a` 不为 `NULL`，结果为 `a`。

```sql
SELECT {fn IFNULL(a, b) } FROM TABLE;

```

## LEFT 函数

`LEFT` 函数的语法如下：

```sql
{fn LEFT (string_exp, n)}

```

`LEFT` 函数从 `string_exp` 中从头开始截取 `n` 个字符长度的字符串。

示例如下：

```sql
SELECT {fn LEFT('helloword', 5)} FROM DUAL; -- 结果为 hello

```

## SUBSTRING 函数

`SUBSTRING` 函数的语法如下：

```sql
{fn SUBSTRING (string_exp, start, length**) }

```

`SUBSTRING` 函数从 `string_exp` 截取字符串，起始位置为 `start`, 长度为 `length`。其中 `start=0` 或者 `start=1` 都表示从的 `string_exp` 起始字符开始。其中 `length` 是可选参数，如果没有指定 `length`，那么长度就直到 `string_exp` 结尾。

示例如下：

```sql
SELECT {fn SUBSTRING('helloword', 5, 4)} FROM DUAL;  -- 结果为 owor
SELECT {fn SUBSTRING('helloword', 5)} FROM DUAL;     -- 结果为 oword
SELECT {fn SUBSTRING('helloword', 1)} FROM DUAL;     -- 结果为 helloword
SELECT {fn SUBSTRING('helloword', 0)} FROM DUAL;     -- 结果为 helloword

```

## 综合示例

```sql
ODBC_TEST(test_ifnull)
{
  SQLRETURN ret = 0;
  SQLSMALLINT cnt;
  char buf[1024] = { 0 };
  char buf1[1024] = { 0 };
  SQLCHAR * drop_sql = (SQLCHAR *)"DROP TABLE type_number";
  SQLExecDirect(Stmt, (SQLCHAR*)drop_sql, (SQLINTEGER)strlen((char*)drop_sql));
  OK_SIMPLE_STMT(Stmt, "CREATE TABLE type_number(a varchar2(100), b varchar2(100))");
  OK_SIMPLE_STMT(Stmt, "insert into type_number values(NULL, 'my name is a')");

  OK_SIMPLE_STMT(Stmt, "select {FN IFNULL(NULL, 100)} from dual");
  CHECK_STMT_RC(Stmt, ret = SQLNumResultCols(Stmt, &cnt));
  CHECK_STMT_RC(Stmt, ret = SQLBindCol(Stmt, 1, SQL_C_CHAR, buf, 1024, NULL));
  CHECK_STMT_RC(Stmt, ret = SQLFetch(Stmt));
  CHECK_STMT_RC(Stmt, ret = SQLFreeStmt(Stmt, SQL_CLOSE));
  printf("%s\n", buf);

  OK_SIMPLE_STMT(Stmt, "select {FN LEFT('helloword', 1)} from dual");
  CHECK_STMT_RC(Stmt, ret = SQLNumResultCols(Stmt, &cnt));
  CHECK_STMT_RC(Stmt, ret = SQLBindCol(Stmt, 1, SQL_C_CHAR, buf, 1024, NULL));
  CHECK_STMT_RC(Stmt, ret = SQLFetch(Stmt));
  CHECK_STMT_RC(Stmt, ret = SQLFreeStmt(Stmt, SQL_CLOSE));
  printf("%s\n", buf);

  OK_SIMPLE_STMT(Stmt, "select {FN SUBSTRING ('helloword', 5, 4)} from dual");
  CHECK_STMT_RC(Stmt, ret = SQLNumResultCols(Stmt, &cnt));
  CHECK_STMT_RC(Stmt, ret = SQLBindCol(Stmt, 1, SQL_C_CHAR, buf, 1024, NULL));
  CHECK_STMT_RC(Stmt, ret = SQLFetch(Stmt));
  CHECK_STMT_RC(Stmt, ret = SQLFreeStmt(Stmt, SQL_CLOSE));
  printf("%s\n", buf);

  OK_SIMPLE_STMT(Stmt, "select {FN SUBSTRING ('helloword', 5)} from dual");
  CHECK_STMT_RC(Stmt, ret = SQLNumResultCols(Stmt, &cnt));
  CHECK_STMT_RC(Stmt, ret = SQLBindCol(Stmt, 1, SQL_C_CHAR, buf, 1024, NULL));
  CHECK_STMT_RC(Stmt, ret = SQLFetch(Stmt));
  CHECK_STMT_RC(Stmt, ret = SQLFreeStmt(Stmt, SQL_CLOSE));
  printf("%s\n", buf);

  OK_SIMPLE_STMT(Stmt, "select {FN LEFT(b, 5)},b from type_number");
  CHECK_STMT_RC(Stmt, ret = SQLNumResultCols(Stmt, &cnt));
  CHECK_STMT_RC(Stmt, ret = SQLBindCol(Stmt, 1, SQL_C_CHAR, buf, 1024, NULL));
  CHECK_STMT_RC(Stmt, ret = SQLBindCol(Stmt, 2, SQL_C_CHAR, buf1, 1024, NULL));
  CHECK_STMT_RC(Stmt, ret = SQLFetch(Stmt));
  CHECK_STMT_RC(Stmt, ret = SQLFreeStmt(Stmt, SQL_CLOSE));
  printf("%s,%s\n", buf, buf1);

  OK_SIMPLE_STMT(Stmt, "select {FN IFNULL(a, 5)},b from type_number");
  CHECK_STMT_RC(Stmt, ret = SQLNumResultCols(Stmt, &cnt));
  CHECK_STMT_RC(Stmt, ret = SQLBindCol(Stmt, 1, SQL_C_CHAR, buf, 1024, NULL));
  CHECK_STMT_RC(Stmt, ret = SQLBindCol(Stmt, 2, SQL_C_CHAR, buf1, 1024, NULL));
  CHECK_STMT_RC(Stmt, ret = SQLFetch(Stmt));
  CHECK_STMT_RC(Stmt, ret = SQLFreeStmt(Stmt, SQL_CLOSE));
  printf("%s,%s\n", buf, buf1);

  return OK;
}

```

 上一篇 下一篇 ![有帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*y6ocSqN8cqsAAAAAAAAAAAAAARQnAQ)![无帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*BG9IQJyLHF8AAAAAAAAAAAAAARQnAQ)![反馈](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*eTWdQKCRKHwAAAAAAAAAAAAAARQnAQ)[AI](https://www.oceanbase.com/obi) 咨询热线