Using String functions in queries

There are various built-in functions on strings. In any string, position starts at 0 and ends at length - 1.

If you want to follow along with the examples, see Sample data to run queries to view a sample data and use the scripts to load sample data for testing. The scripts create the tables used in the examples and load data into the tables.

If you want to follow along with the examples, see Sample data to run queries to view a sample data and learn how to use OCI console to create the example tables and load data using JSON files.

substring function

The substring function extracts a string from a given string according to a given numeric starting position and a given numeric substring length.

returnvalue substring (source, position [, substring_length] )

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

Example:Fetch the first three characters from the routing details of a passenger with ticket number 1762376407826.

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

Output:

{"Source":"JFK"}

concat function

The concat function concatenates all its arguments and displays the concatenated string as output.

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

Example: Display the routing of a customer with a particular ticket number as “The route for passenger_name is …”.

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

Output:

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

upper and lower functions

The upper and lower are simple functions to convert to fully upper case or lower case respectively. The upper function converts all the characters in a string to uppercase. Thelower function converts all the characters in a string to lowercase.

returnvalue upper (source)
returnvalue lower (source)

source ::= any*
returnvalue ::= string

Example 1: Fetch the full name of the passenger in uppercase whose ticket number is 1762376407826.

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

Output:

{"FULLNAME_CAPITALS":"DIERDRE AMADOR"}

Example 2:Fetch the full name of the passenger in lowercase whose ticket number is 1762376407826.

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

Output:

{"fullname_lowercase":"dierdre amador"}

trim function

The trim function enables you to trim leading or trailing characters (or both) from a string. The ltrim function enables you to trim leading characters from a string. The rtrim function enables you to trim trailing characters from a string.

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

Example: Remove leading and trailing blank spaces from the route details of the passenger whose ticket number is 1762350390409.

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

Output:

{"Column_1":"JFK/MAD"}

Using ltrim function to remove leading spaces:

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

Output:

{"Column_1":"JFK/MAD"}

Using rtrim function to remove trailing spaces:

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

Output:

{"Column_1":"JFK/MAD"}

length function

The length function returns the length of a character string. The length function calculates the length using the UTF character set.

returnvalue length(source)

source ::= any*
returnvalue ::= integer

Example:Find the length of the full name of the passenger whose ticket number is 1762350390409.

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

Output:

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

contains function

The contains function indicates whether or not a search string is present inside the source string.

returnvalue contains(source, search_string)

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

Example:Fetch the full names of passengers who have “SFO” in their route.

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

Output:

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

starts_with and ends_with functions

The starts_with function indicates whether or not the source string begins with the search string.

returnvalue starts_with(source, search_string)

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

The ends_withfunction indicates whether or not the source string ends with the search string.

returnvalue ends_with(source, search_string)

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

Example:How long does it take from the time of check-in to the time the bag is scanned at the point of boarding for the passenger with ticket number 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)

Explanation:In the baggage data, every flightLeg has an actions array. There are three different actions in the actions array. The action code for the first element in the array is Checkin/Offload. For the first leg, the action code is Checkin and for the other legs, the action code is Offload at the hop. The action code for the second element of the array is BagTag Scan. In the query above, you determine the difference in action time between the bag tag scan and check-in time. You use the contains function to filter the action time only if the action code is Checkin or BagScan. Since only the first flight leg has details of check-in and bag scan, you additionally filter the data using starts_with function to fetch only the source code fltRouteSrc.

Output:

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

Example 2 :Find list of passengers whose destination is JTR.

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

Output:

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

index_of function

The index_of function determines the position of the first character of the search string at its first occurrence if any.

returnvalue index_of(source, search_string [, start_position])

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

Various return values:

Returns the position of the first character of the search string at its first occurrence. The position is relative to the start position of the string (which is zero).
Returns -1 if search_string is not present in the source.
Returns 0 for any value of source if the search_string is of length 0.
Returns NULL if any argument is NULL.
Returns NULL if any argument is an empty sequence or a sequence with more than one item.
Returns error if start_position argument is not an integer.

Example 1: Determine at which position “-“ is found in the estimated arrival time of the first leg for the passenger with ticket number 1762320569757.

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

Output:

{"Column_1":4}

Example 2: Determine at which position “/” is found in the routing of the first leg for passenger with ticket number 1762320569757. This will help you determine how many characters are there for the source point for the passenger with ticket number 1762320569757.

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

Output:

"Column_1":3}

replace function

The replace function returns the source with every occurrence of the search string replaced with the replacement string.

returnvalue replace(source, search_string [, replacement_string])

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

Example:Replace the source location of the passenger with ticket number 1762320569757 from SFO to SOF.

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

Output:

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

Example 2:Replace the double quote in the passenger name with a single quote.

If your data might contain a double quote in the passenger’s name, you can use replace function to change the double quote to a single quote.

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

reverse function

The reverse function returns the characters of the source string in reverse order, where the string is written beginning with the last character first.

returnvalue reverse(source)

source ::= any*
returnvalue ::= string

Example: Display the full name and reverse the full name of the passenger with ticket number 1762330498104.

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

Output:

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

Examples using QueryRequest API

You can use QueryRequest API and apply SQL functions to fetch data from a NoSQL table.

To execute your query, you use the 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);

To execute your query use the borneo.NoSQLHandle.query() method.

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)

To execute a query use the Client.Query function.

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)

To execute a query use query method.

JavaScript: Download the full code SQLFunctions.js from the examples here.

  //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: Download the full code SQLFunctions.ts from the examples here.

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

To execute a query, you may call QueryAsync method or call GetQueryAsyncEnumerable method and iterate over the resulting async enumerable.

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

Using String functions in queries

substring function

concat function

upper and lower functions

trim function

length function

contains function

starts_with and ends_with functions

index_of function

replace function

reverse function

Examples using QueryRequest API

Related Topics