Functions on Strings

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, download the script baggageschema_loaddata.sql and execute it as shown below. This script creates the table used in the example and loads data into the table.

Start your KVSTORE or KVLite and open the SQL.shell.

java -jar lib/kvstore.jar kvlite -secure-config disable
java -jar lib/sql.jar -helper-hosts localhost:5000 -store kvstore

Using the load command, execute the script.

load -file baggageschema_loaddata.sql

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