在查询中使用字符串函数

字符串上有各种内置函数。在任何字符串中，位置从 0 开始，以长度 - 1 结束。

如果要跟进示例，请参阅运行查询的示例数据以查看示例数据并使用脚本加载示例数据进行测试。这些脚本将创建示例中使用的表，并将数据加载到表中。

如果要跟进示例，请参阅运行查询的示例数据以查看示例数据，并了解如何使用 OCI 控制台创建示例表和使用 JSON 文件加载数据。

substring 函数

substring 函数根据给定的数字起始位置和给定的数字子字符串长度从给定的字符串中提取字符串。

returnvalue substring (source, position [, substring_length] )

source ::= any*
position ::= integer*
substring_length ::= integer*
returnvalue ::= string

示例：从票号为 1762376407826 的乘客的路线详细信息中提取前三个字符。

SELECT substring(bag.baginfo.routing,0,3) AS Source
FROM baggageInfo bag
WHERE ticketNo=1762376407826

输出：

{"Source":"JFK"}

concat 函数

concat 函数连接其所有参数，并将连接字符串显示为输出。

returnvalue concat (source,[source*])
source ::= any*
returnvalue ::= string

示例：将具有特定票证编号的客户路由显示为“The route for 乘客 _name is …”。

SELECT concat("The route for passenger ",fullName , " is ", bag.baginfo[0].routing)
FROM baggageInfo bag
WHERE ticketNo=1762376407826

输出：

{"Column_1":"The route for passenger Dierdre Amador is JFK/MAD"}

上限和下限函数

upper 和 lower 是分别转换为大写或小写的简单函数。upper 函数将字符串中的所有字符转换为大写的字符。lower 函数将字符串中的所有字符转换为小写的字符。

returnvalue upper (source)
returnvalue lower (source)

source ::= any*
returnvalue ::= string

示例 1：以大写形式获取票证编号为 1762376407826 的乘客的全名。

SELECT upper(fullname) AS FULLNAME_CAPITALS
FROM BaggageInfo
WHERE ticketNo=1762376407826

输出：

{"FULLNAME_CAPITALS":"DIERDRE AMADOR"}

示例 2：以小写形式获取票证编号为 1762376407826 的乘客的全名。

SELECT lower(fullname) AS fullname_lowercase
FROM BaggageInfo WHERE ticketNo=1762376407826

输出：

{"fullname_lowercase":"dierdre amador"}

trim 函数

使用 trim 函数可以从字符串中截去前导字符或尾随字符串（或两者）。使用 ltrim 函数可以从字符串中截取前导字符。使用 rtrim 函数可以从字符串中截取结尾字符。

returnvalue trim(source [, position [, trim_character]])

source ::= any*
position ::= "leading"|"trailing"|"both"
trim_character ::= string*
returnvalue ::= string
returnvalue ltrim(source)

returnvalue rtrim(source)
source ::= any*
returnvalue ::= string

示例：从票证编号为 1762350390409 的乘客的路线详细信息中删除前导和尾随空格。

SELECT trim(bag.baginfo[0].routing,"trailing"," ")
FROM BaggageInfo bag
WHERE ticketNo=1762376407826

输出：

{"Column_1":"JFK/MAD"}

使用 ltrim 函数删除前导空格：

SELECT ltrim(bag.baginfo[0].routing)
FROM BaggageInfo bag
WHERE ticketNo=1762376407826

输出：

{"Column_1":"JFK/MAD"}

使用 rtrim 函数删除尾随空格：

SELECT rtrim(bag.baginfo[0].routing)
FROM BaggageInfo bag
WHERE ticketNo=1762376407826

输出：

{"Column_1":"JFK/MAD"}

length 函数

length 函数返回字符串的长度。length 函数使用 UTF 字符集计算长度。

returnvalue length(source)

source ::= any*
returnvalue ::= integer

示例：查找票证编号为 1762350390409 的乘客的全名长度。

SELECT fullname, length(fullname) AS fullname_length
FROM BaggageInfo
WHERE ticketNo=1762350390409

输出：

{"fullname":"Fallon Clements","fullname_length":15}

包含函数

contains 函数指示源字符串中是否存在搜索字符串。

returnvalue contains(source, search_string)

source ::= any*
search_string ::= any*
returnvalue ::= boolean

示例：获取在路线中具有“SFO”的乘客的全名。

SELECT fullname FROM baggageInfo bag
WHERE EXISTS bag.bagInfo[contains($element.routing,"SFO")]

输出：

{"fullname":"Michelle Payne"}
{"fullname":"Lucinda Beckman"}
{"fullname":"Henry Jenkins"}
{"fullname":"Lorenzo Phil"}
{"fullname":"Gerard Greene"}

begin_with 和 ends_with 函数

starts_with 函数指示源字符串是否以搜索字符串开头。

returnvalue starts_with(source, search_string)

source ::= any*
search_string ::= any*
returnvalue ::= boolean

ends_with 函数指示源字符串是否以搜索字符串结尾。

returnvalue ends_with(source, search_string)

source ::= any*
search_string ::= any*
returnvalue ::= boolean

示例：从办理登机手续到为票号为 176234463813 的乘客在登机时扫描行李需要多长时间？

SELECT $flightLeg.flightNo,
$flightLeg.actions[contains($element.actionCode, "Checkin")].actionTime AS checkinTime,
$flightLeg.actions[contains($element.actionCode, "BagTag Scan")].actionTime AS bagScanTime,
timestamp_diff(
   $flightLeg.actions[contains($element.actionCode, "Checkin")].actionTime,
   $flightLeg.actions[contains($element.actionCode, "BagTag Scan")].actionTime
) AS diff
FROM baggageinfo $s, $s.bagInfo[].flightLegs[] AS $flightLeg
WHERE ticketNo=176234463813
AND starts_with($s.bagInfo[].routing, $flightLeg.fltRouteSrc)

解释：在行李数据中，每个 flightLeg 都有一个操作数组。操作数组中有三个不同的操作。数组中第一个元素的操作代码是 Checkin/Offload。对于第一个行程，操作代码为“签入”，对于其他行程，操作代码为“在跃点卸载”。数组的第二个元素的操作代码是 BagTag Scan。在上面的查询中，您可以确定袋标签扫描和检入时间之间的操作时间差异。仅当操作代码为 Checkin 或 BagScan 时，才使用 contains 函数筛选操作时间。由于只有第一个航班段有登记入住和行李扫描的详细信息，因此您还可以使用 starts_with 函数过滤数据，以便仅提取源代码 fltRouteSrc。

输出：

{"flightNo":"BM572","checkinTime":"2019-03-02T03:28:00Z",
"bagScanTime":"2019-03-02T04:52:00Z","diff":-5040000}

示例 2：查找目标为 JTR 的乘客列表。

SELECT fullname FROM baggageInfo $bagInfo
WHERE ends_with($bagInfo.bagInfo[].routing, "JTR")

输出：

{"fullname":"Lucinda Beckman"}
{"fullname":"Gerard Greene"}
{"fullname":"Michelle Payne"}

index_of 函数

index_of 函数确定搜索字符串中第一个字符（如果有）的位置。

returnvalue index_of(source, search_string [, start_position])

source ::= any*
search_string ::= any*
start_position ::= integer*
returnvalue ::= integer

各种返回值：

示例 1：确定在票号为 1762320569757 的乘客的第一段预计到达时间中找到“-”的位置。

SELECT index_of(bag.baginfo.flightLegs[0].estimatedArrival,"-")
FROM BaggageInfo bag
WHERE ticketNo=1762320569757

输出：

{"Column_1":4}

示例 2：确定在票证编号为 1762320569757 的乘客的第一行程中找到的位置 "/" 的位置。这将帮助您确定票号为 1762320569757 的乘客的来源点有多少字符。

SELECT index_of(bag.baginfo.routing,"/")
FROM BaggageInfo bag
WHERE ticketNo=1762320569757

输出：

"Column_1":3}

替换函数

replace 函数返回每个出现的搜索字符串替换为替换字符串的源。

returnvalue replace(source, search_string [, replacement_string])

source ::= any*
search_string ::= any*
replacement_string ::= any*
returnvalue ::= string

示例：将票证编号为 1762320569757 的乘客的来源地点从 SFO 替换为 SOF 。

SELECT replace(bag.bagInfo[0].routing,"SFO","SOF")
FROM baggageInfo bag
WHERE ticketNo=1762320569757

输出：

{"Column_1":"SOF/IST/ATH/JTR"}

示例 2：将乘客姓名中的双引号替换为单引号。

如果您的数据可能包含乘客姓名中的双引号，您可以使用替换函数将双引号更改为单引号。

SELECT fullname,
replace(fullname, """, "'") as new_fullname
FROM BaggageInfo bag

反向函数

reverse 函数按相反的顺序返回源字符串的字符，其中字符串首先以最后一个字符开头。

returnvalue reverse(source)

source ::= any*
returnvalue ::= string

示例：显示客票号为 1762330498104 的全名并反转客票的全名。

SELECT fullname, reverse(fullname)
FROM baggageInfo
WHERE ticketNo=1762330498104

输出：

{"fullname":"Michelle Payne","Column_2":"enyaP ellehciM"}

使用 QueryRequest API 的示例

您可以使用 QueryRequest API 和应用 SQL 函数从 NoSQL 表提取数据。

要执行查询，请使用 NoSQLHandle.query() API。

Download the full code SQLFunctions.java from the examples here.

 //Fetch rows from the table
private static void fetchRows(NoSQLHandle handle,String sqlstmt) throws Exception {
   try (
      QueryRequest queryRequest = new QueryRequest().setStatement(sqlstmt);
      QueryIterableResult results = handle.queryIterable(queryRequest)){
      for (MapValue res : results) {
         System.out.println("\t" + res);
      }
   }
}
String string_func1="SELECT substring(bag.baginfo.routing,0,3) AS Source FROM baggageInfo bag WHERE ticketNo=1762376407826";
System.out.println("Using substring function ");
fetchRows(handle,string_func1);
String string_func2="SELECT fullname, length(fullname) AS fullname_length FROM BaggageInfo WHERE ticketNo=1762320369957";
System.out.println("Using length function ");
fetchRows(handle,string_func2);
String string_func3="SELECT fullname FROM baggageInfo bag WHERE EXISTS bag.bagInfo[contains($element.routing,\"SFO\")]";
System.out.println("Using contains function ");
fetchRows(handle,string_func3);

要执行查询，请使用 borneo.NoSQLHandle.query() 方法。

Download the full code SQLFunctions.py from the examples here.

# Fetch data from the table
def fetch_data(handle,sqlstmt):
   request = QueryRequest().set_statement(sqlstmt)
   print('Query results for: ' + sqlstmt)
   result = handle.query(request)
   for r in result.get_results():
      print('\t' + str(r))
string_func1 = '''SELECT substring(bag.baginfo.routing,0,3) AS Source FROM baggageInfo bag
                    WHERE ticketNo=1762376407826'''
print('Using substring function:')
fetch_data(handle,string_func1)

string_func2 = '''SELECT fullname, length(fullname) AS fullname_length FROM BaggageInfo
                    WHERE ticketNo=1762320369957'''
print('Using length function:')
fetch_data(handle,string_func2)

string_func3 = '''SELECT fullname FROM baggageInfo bag WHERE
                    EXISTS bag.bagInfo[contains($element.routing,"SFO")]'''
print('Using contains function:')
fetch_data(handle,string_func3)

要执行查询，请使用 Client.Query 函数。

Download the full code SQLFunctions.go from the examples here.

 //fetch data from the table
func fetchData(client *nosqldb.Client, err error, tableName string, querystmt string)(){
   prepReq := &nosqldb.PrepareRequest{
		Statement: querystmt,
   }
   prepRes, err := client.Prepare(prepReq)
   if err != nil {
      fmt.Printf("Prepare failed: %v\n", err)
      return
   }
   queryReq := &nosqldb.QueryRequest{
		 PreparedStatement: &prepRes.PreparedStatement,   }
   var results []*types.MapValue
   for {
      queryRes, err := client.Query(queryReq)
      if err != nil {
         fmt.Printf("Query failed: %v\n", err)
  	return
      }
      res, err := queryRes.GetResults()
      if err != nil {
         fmt.Printf("GetResults() failed: %v\n", err)
  	return
      }
      results = append(results, res...)
      if queryReq.IsDone() {
         break
      }
   }
   for i, r := range results {
      fmt.Printf("\t%d: %s\n", i+1, jsonutil.AsJSON(r.Map()))
   }
}
string_func1 := `SELECT substring(bag.baginfo.routing,0,3) AS Source FROM baggageInfo bag
                    WHERE ticketNo=1762376407826`
fmt.Printf("Using substring function:\n")
fetchData(client, err,tableName,string_func1)

string_func2 := `SELECT fullname, length(fullname) AS fullname_length FROM BaggageInfo
                    WHERE ticketNo=1762320369957`
fmt.Printf("Using length function:\n")
fetchData(client, err,tableName,string_func2)

string_func3 := `SELECT fullname FROM baggageInfo bag WHERE
                    EXISTS bag.bagInfo[contains($element.routing,"SFO")]`
fmt.Printf("Using contains function:\n")
fetchData(client, err,tableName,string_func3)

要执行查询，请使用 query 方法。

JavaScript：从此处的示例下载完整代码 SQLFunctions.js。

  //fetches data from the table
async function fetchData(handle,querystmt) {
   const opt = {};
   try {
      do {
         const result = await handle.query(querystmt, opt);
         for(let row of result.rows) {
            console.log('  %O', row);
         }
         opt.continuationKey = result.continuationKey;
      } while(opt.continuationKey);
   } catch(error) {
      console.error('  Error: ' + error.message);
   }
}

TypeScript：从此处的示例下载完整代码 SQLFunctions.ts。

interface StreamInt {
   acct_Id: Integer;
   profile_name: String;
   account_expiry: TIMESTAMP;
   acct_data: JSON;
}
/* fetches data from the table */
async function fetchData(handle: NoSQLClient,querystmt: any) {
   const opt = {};
   try {
      do {
         const result = await handle.query<StreamInt>(querystmt, opt);
         for(let row of result.rows) {
            console.log('  %O', row);
         }
         opt.continuationKey = result.continuationKey;
      } while(opt.continuationKey);
   } catch(error) {
      console.error('  Error: ' + error.message);
   }
}
const string_func1 = `SELECT substring(bag.baginfo.routing,0,3) AS Source FROM baggageInfo bag
                      WHERE ticketNo=1762376407826`
console.log("Using substring function:");
await fetchData(handle,string_func1);

const string_func2 = `SELECT fullname, length(fullname) AS fullname_length FROM BaggageInfo
                      WHERE ticketNo=1762320369957`
console.log("Using length function");
await fetchData(handle,string_func2);

const string_func3 = `SELECT fullname FROM baggageInfo bag WHERE
                      EXISTS bag.bagInfo[contains($element.routing,"SFO")]`
console.log("Using contains function");
await fetchData(handle,string_func3);

要执行查询，可以调用 QueryAsync 方法或调用 GetQueryAsyncEnumerable 方法并迭代生成的异步枚举。

Download the full code SQLFunctions.cs from the examples here.

private static async Task fetchData(NoSQLClient client,String querystmt){
   var queryEnumerable = client.GetQueryAsyncEnumerable(querystmt);
   await DoQuery(queryEnumerable);
}

private static async Task DoQuery(IAsyncEnumerable<QueryResult<RecordValue>> queryEnumerable){
   Console.WriteLine("  Query results:");
   await foreach (var result in queryEnumerable) {
      foreach (var row in result.Rows)
      {
         Console.WriteLine();
         Console.WriteLine(row.ToJsonString());
      }
   }
}
private const string string_func1 =@"SELECT substring(bag.baginfo.routing,0,3) AS Source FROM baggageInfo bag
                                           WHERE ticketNo=1762376407826" ;
Console.WriteLine("\nUsing substring function!");
await fetchData(client,string_func1);

private const string string_func2 =@"SELECT fullname, length(fullname) AS fullname_length FROM BaggageInfo
                                           WHERE ticketNo=1762320369957";
Console.WriteLine("\nUsing length function!");
await fetchData(client,string_func2);


private const string string_func3 =@"SELECT fullname FROM baggageInfo bag WHERE
                                           EXISTS bag.bagInfo[contains($element.routing,""SFO"")]";
Console.WriteLine("\nUsing contains function!");
await fetchData(client,string_func3);

相关主题