Utiliser des fonctions de type chaîne dans des interrogations

Il existe différentes fonctions intégrées sur les chaînes. Dans n'importe quelle chaîne, la position commence à 0 et se termine à longueur - 1.

Si vous voulez suivre les exemples, voir pour voir des données-échantillons et utiliser les scripts pour charger des données-échantillons à des fins de test. Les scripts créent les tables utilisées dans les exemples et chargent les données dans les tables.

Si vous voulez suivre les exemples, voir Exemples de données pour exécuter des interrogations pour voir des exemples de données et apprendre à utiliser la console OCI pour créer les exemples de tables et charger des données à l'aide de fichiers JSON.

Rubriques connexes

fonction de sous-chaîne

La fonction substring extrait une chaîne d'une chaîne donnée en fonction d'une position de début numérique donnée et d'une longueur de sous-chaîne numérique donnée. 
returnvalue substring (source, position [, substring_length] )

source ::= any*
position ::= integer*
substring_length ::= integer*
returnvalue ::= string
Exemple : Extraire les trois premiers caractères des détails d'acheminement d'un passager avec le numéro de billet 1762376407826.
SELECT substring(bag.baginfo.routing,0,3) AS Source 
FROM baggageInfo bag 
WHERE ticketNo=1762376407826
Sortie : 
{"Source":"JFK"}

fonction concat

La fonction concat concatène tous ses arguments et affiche la chaîne concaténée en tant que sortie.
returnvalue concat (source,[source*])
source ::= any*
returnvalue ::= string
Exemple : Affiche l'acheminement d'un client avec un numéro de ticket particulier comme suit : "L'acheminement pour passenger_name est ...".
SELECT concat("The route for passenger ",fullName , " is ", bag.baginfo[0].routing)
FROM baggageInfo bag 
WHERE ticketNo=1762376407826
Sortie : 
{"Column_1":"The route for passenger Dierdre Amador is JFK/MAD"}

fonctions supérieure et inférieure

upper et lower sont des fonctions simples à convertir en majuscules ou en minuscules, respectivement. La fonction upper convertit tous les caractères d'une chaîne en majuscules. La fonction lower convertit tous les caractères d'une chaîne en minuscules.
returnvalue upper (source)
returnvalue lower (source) 

source ::= any* 
returnvalue ::= string
Exemple 1 : Extrayez le nom complet du passager en majuscules dont le numéro de billet est 1762376407826.
SELECT upper(fullname) AS FULLNAME_CAPITALS 
FROM BaggageInfo 
WHERE ticketNo=1762376407826
Sortie : 
{"FULLNAME_CAPITALS":"DIERDRE AMADOR"}
Exemple 2 : Extraire le nom complet du passager en minuscules dont le numéro de billet est 1762376407826.
SELECT lower(fullname) AS fullname_lowercase
FROM BaggageInfo WHERE ticketNo=1762376407826
Sortie :
{"fullname_lowercase":"dierdre amador"}

fonction de trim

La fonction trim vous permet de supprimer les caractères de début, de fin (ou les deux) d'une chaîne. La fonction ltrim vous permet de rogner les caractères de début d'une chaîne. La fonction rtrim vous permet de rogner les caractères de fin d'une chaîne.
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
Exemple : Supprimez les espaces de début et de fin des détails d'itinéraire du passager dont le numéro de billet est 1762350390409.
SELECT trim(bag.baginfo[0].routing,"trailing"," ")
FROM BaggageInfo bag 
WHERE ticketNo=1762376407826
Sortie : 
{"Column_1":"JFK/MAD"}
Utilisation de la fonction ltrim pour supprimer les espaces au début :
SELECT ltrim(bag.baginfo[0].routing)
FROM BaggageInfo bag 
WHERE ticketNo=1762376407826
Sortie : 
{"Column_1":"JFK/MAD"}
Utilisation de la fonction rtrim pour supprimer les espaces de fin :
SELECT rtrim(bag.baginfo[0].routing)
FROM BaggageInfo bag 
WHERE ticketNo=1762376407826
Sortie : 
{"Column_1":"JFK/MAD"}

fonction de longueur

La fonction length retourne la longueur d'une chaîne de caractères. La fonction de longueur calcule la longueur à l'aide du jeu de caractères UTF.
returnvalue length(source)

source ::= any*
returnvalue ::= integer
Exemple : Recherchez la longueur du nom complet du passager dont le numéro de billet est 1762350390409.
SELECT fullname, length(fullname) AS fullname_length 
FROM BaggageInfo
WHERE ticketNo=1762350390409
Sortie : 
{"fullname":"Fallon Clements","fullname_length":15}

contient une fonction

La fonction contains indique si une chaîne de recherche est présente ou non dans la chaîne source.
returnvalue contains(source, search_string)

source ::= any*
search_string ::= any*
returnvalue ::= boolean
Exemple : Extraire le nom complet des passagers qui ont "SFO" dans leur itinéraire.
SELECT fullname FROM baggageInfo bag 
WHERE EXISTS bag.bagInfo[contains($element.routing,"SFO")]
Sortie : 
{"fullname":"Michelle Payne"}
{"fullname":"Lucinda Beckman"}
{"fullname":"Henry Jenkins"}
{"fullname":"Lorenzo Phil"}
{"fullname":"Gerard Greene"}

Fonctions starts_with et ends_with

La fonction starts_with indique si la chaîne source commence ou non par la chaîne de recherche.
returnvalue starts_with(source, search_string)

source ::= any*
search_string ::= any*
returnvalue ::= boolean
La fonction ends_with indique si la chaîne source se termine ou non par la chaîne de recherche.
returnvalue ends_with(source, search_string)

source ::= any*
search_string ::= any*
returnvalue ::= boolean
Exemple : Combien de temps faut-il entre le moment de l'enregistrement et le moment où le sac est balayé au point d'embarquement pour le passager avec le numéro de billet 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)

Explication : Dans les données des bagages, chaque flightLeg comporte un tableau d'actions. Il existe trois actions différentes dans le tableau d'actions. Le code d'action du premier élément du tableau est Checkin/Offload. Pour la première étape, le code d'action est Checkin et pour les autres étapes, le code d'action est Offload at the hop. Le code d'action du deuxième élément du tableau est le balayage BagTag. Dans l'interrogation ci-dessus, vous déterminez la différence de temps d'action entre le balayage de l'étiquette d'entité composite et le temps d'enregistrement. Vous utilisez la fonction contains pour filtrer le temps d'action uniquement si le code d'action est Archiver ou BagScan. Comme seule la première portion de vol contient les détails de l'enregistrement et du balayage d'entité composite, vous filtrez également les données à l'aide de la fonction starts_with pour extraire uniquement le code source fltRouteSrc.

Sortie : 
{"flightNo":"BM572","checkinTime":"2019-03-02T03:28:00Z",
"bagScanTime":"2019-03-02T04:52:00Z","diff":-5040000}
Exemple 2 : Recherchez la liste des passagers dont la destination est JTR.
SELECT fullname FROM baggageInfo $bagInfo 
WHERE ends_with($bagInfo.bagInfo[].routing, "JTR")
Sortie :
{"fullname":"Lucinda Beckman"}
{"fullname":"Gerard Greene"}
{"fullname":"Michelle Payne"}

Fonction index_of

La fonction index_of détermine la position du premier caractère de la chaîne de recherche à sa première occurrence, le cas échéant.
returnvalue index_of(source, search_string [, start_position])

source ::= any*
search_string ::= any*
start_position ::= integer*
returnvalue ::= integer
Différentes valeurs de retour :
  • Retourne la position du premier caractère de la chaîne de recherche à sa première occurrence. La position est relative à la position de début de la chaîne (qui est nulle).
  • Retourne -1 si search_string n'est pas présent dans la source.
  • Retourne 0 pour toute valeur de source si search_string a une longueur de 0.
  • Retourne NULL si un argument est NULL.
  • Retourne NULL si un argument est une séquence vide ou une séquence contenant plus d'un élément.
  • Retourne une erreur si l'argument start_position n'est pas un nombre entier.
Exemple 1 : Déterminez à quelle position "-" se trouve dans l'heure d'arrivée estimative de la première étape pour le passager avec le numéro de billet 1762320569757.
SELECT index_of(bag.baginfo.flightLegs[0].estimatedArrival,"-")
FROM BaggageInfo bag 
WHERE ticketNo=1762320569757
Sortie : 
{"Column_1":4}
Exemple 2 : Déterminez à quelle position "/" se trouve dans le routage de la première étape pour le passager avec le numéro de billet 1762320569757. Cela vous aidera à déterminer le nombre de caractères présents pour le point d'origine du passager avec le numéro de billet 1762320569757.
SELECT index_of(bag.baginfo.routing,"/") 
FROM BaggageInfo bag 
WHERE ticketNo=1762320569757
Sortie : 
"Column_1":3}

remplacer la fonction

La fonction replace retourne la source avec chaque occurrence de la chaîne de recherche remplacée par la chaîne de remplacement. 
returnvalue replace(source, search_string [, replacement_string])

source ::= any*
search_string ::= any*
replacement_string ::= any*
returnvalue ::= string
Exemple : Remplacez l'emplacement d'origine du passager par le numéro de billet 1762320569757 de SFO à SOF.
SELECT replace(bag.bagInfo[0].routing,"SFO","SOF") 
FROM baggageInfo bag
WHERE ticketNo=1762320569757
Sortie :
{"Column_1":"SOF/IST/ATH/JTR"}

Exemple 2 : Remplacez le guillemet double dans le nom du passager par un guillemet simple.

Si vos données peuvent contenir un guillemet double au nom du passager, vous pouvez utiliser la fonction de remplacement pour remplacer le guillemet double par un guillemet simple.
SELECT fullname, 
replace(fullname, "\"", "'") as new_fullname
FROM BaggageInfo bag

fonction inverse

La fonction reverse retourne les caractères de la chaîne source dans l'ordre inverse, où la chaîne est écrite en commençant par le dernier caractère.
returnvalue reverse(source)

source ::= any*
returnvalue ::= string
Exemple : Affichez le nom complet et inversez le nom complet du passager avec le numéro de billet 1762330498104.
SELECT fullname, reverse(fullname) 
FROM baggageInfo
WHERE ticketNo=1762330498104
Sortie : 
{"fullname":"Michelle Payne","Column_2":"enyaP ellehciM"}

Exemples utilisant l'API QueryRequest

Vous pouvez utiliser l'API QueryRequest et appliquer des fonctions SQL pour extraire des données d'une table NoSQL.

Pour exécuter une interrogation, utilisez l'API NoSQLHandle.query().

Téléchargez le code complet SQLFunctions.java dans les exemples ici.
 //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);

Pour exécuter votre interrogation, utilisez la méthode borneo.NoSQLHandle.query().

Téléchargez le code complet SQLFunctions.py dans les exemples ici.
# 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)

Pour exécuter une interrogation, utilisez la fonction Client.Query.

Téléchargez le code complet SQLFunctions.go dans les exemples ici.
 //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)

Pour exécuter une interrogation, utilisez la méthode query.

JavaScript : Téléchargez le code complet SQLFunctions.js à partir des exemples ici.
  //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 : Téléchargez le code complet SQLFunctions.ts à partir des exemples ici.
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);

Pour exécuter une interrogation, vous pouvez appeler la méthode QueryAsync ou appeler la méthode GetQueryAsyncEnumerable et effectuer une itération sur l'énumération asynchrone obtenue.

Téléchargez le code complet SQLFunctions.cs dans les exemples ici.
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);