問合せでの文字列ファンクションの使用

様々な文字列の組込み関数があります。任意の文字列で、位置は0から始まり、長さ- 1で終了します。

例とあわせて実行する場合は、を参照してサンプル・データを表示し、スクリプトを使用してテスト用のサンプル・データをロードします。スクリプトによって、例で使用する表が作成され、表にデータがロードされます。

例に従う場合は、問合せを実行するサンプル・データを参照してサンプル・データを表示し、OCIコンソールを使用してサンプル表を作成し、JSONファイルを使用してデータをロードする方法を学習します。

• substring関数
• concat関数
• upperおよびlower関数
• trim関数
• length関数
• contains関数
• starts_withファンクションおよびends_withファンクション
• index_of関数
• 関数を置換
• reverse関数
• QueryRequest APIを使用した例

substring関数

substring関数は、指定された数値の開始位置および指定された数の部分文字列の長さに従って、指定された文字列から文字列を抽出します。

returnvalue substring (source, position [, substring_length] )

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

例: チケット番号が1762376407826の乗客のルーティング詳細から最初の3文字をフェッチします。

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 passenger_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は、すべて大文字またはすべて小文字にそれぞれ変換する単純な関数です。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関数

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"}

starts_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にも1つのactions配列があります。actions配列には3つの異なるアクションがあります。配列の最初の要素のアクション・コードは、Checkin/Offloadです。最初の区間ではアクション・コードがCheckinとなり、他の区間ではアクション・コードがホップでOffloadとなります。この配列の2番目の要素のアクション・コードはBagTag Scanです。前述の問合せで、手荷物タグ・スキャンとチェックイン時間の間のアクション時間の差異を確認します。アクション・コードが「チェックイン」または「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

様々な戻り値:

• 検索文字列の最初の文字の最初の出現時の位置を返します。位置は、文字列の開始位置(ゼロ)からの相対位置です。
• search_stringがソースに存在しない場合は-1を返します。
• search_stringの長さが0である場合、ソースの値に対して0を返します。
• 引数がNULLの場合はNULLを返します。
• 引数が空の順序であるか、複数の項目がある順序の場合はNULLを返します。
• start_position引数が整数でない場合はエラーを返します。

例1:チケット番号が1762320569757の乗客について、第1区間の推定到着時刻に- が含まれる位置を特定します。

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

出力:

{"Column_1":4}

例2:チケット番号が1762320569757の乗客について、第1区間のルーティングに/が含まれる位置を特定します。これは、チケット番号が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の乗客の出発地を、FOからSOFに置き換えます。

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

出力:

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

例2: 乗客名の二重引用符を一重引用符で置き換えます。

乗客の名前に二重引用符が含まれている可能性がある場合は、replace関数を使用して、二重引用符を単一引用符に変更できます。

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

reverse関数

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表からデータをフェッチできます。

• Java
• Python
• Go
• Node.js
• C#

問合せを実行するには、NoSQLHandle.query() APIを使用します。

ここの例からフル・コードSQLFunctions.javaをダウンロードします。

 //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()メソッドを使用します。

ここの例からフル・コードSQLFunctions.pyをダウンロードします。

# 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関数を使用します。

ここの例からフル・コードSQLFunctions.goをダウンロードします。

 //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メソッドをコールして、結果の非同期列挙可能性に対して反復処理します。

ここの例からフル・コードSQLFunctions.csをダウンロードします。

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);

---

タイトルおよび著作権情報

問合せでの文字列ファンクションの使用

Copyright ©2024,

Oracle and/or its affiliates.