Work with strings

Strings in Deephaven in tables and in Groovy scripts are Java Strings (java.lang.String). In Python scripts, strings are Python strings. However, Python strings are automatically converted to Java Strings and vice-versa when passing strings between the Python script environment and the Deephaven Query Language.

from deephaven.TableTools import emptyTable
s = "Hello World"
x = emptyTable(1).update("Col1=s")
m = x.getMeta()

How to determine data types of columns

As shown above, .getMeta() can be used on a table to determine the data types of columns in a table.

The metadata shows that Col1 is a java.lang.String in the x table.

Another option to quickly check a column's datatype is simply to hover the mouse pointer over a column header:

Manipulating strings

Since Strings in queries, and in Deephaven tables, are Java Strings, their manipulation is accomplished with Java String methods, and using backticks to delimit embedded Strings within a query expression.

The + operator can be used to concatenate Strings:

x = emptyTable(1).update("Col1=`Hello` + ` ` + `World`")

Other Java String methods include substring and toLowerCase.

x = emptyTable(1).update("Col1=`ABCDEF`.substring(2,5).toLowerCase()")

Generally conversion from other types to String when concatenating is handled automatically:

t = db.t("LearnDeephaven" , "StockTrades")\
    .where("Date=`2017-08-23`")\
    .sort("Sym")\
    .renameColumns("Symbol = Sym")\
    .head(10)\
    .select("Timestamp", "Symbol", "Last", "Size", "NewColumn = Last + Source + Size")

In this example, the expression in .select() converted the double value of the Last column and the long value of the Size column to Strings when concatenating them with Source to make NewColumn. Note that this use of .select(), to add a new column based on an expression, is equivalent to using .update().

Note that if a null value is concatenated with a String, Java will return the literal String null instead of the null value.

x = emptyTable(1).update("Col1=`Hello` + null")

Comparing String and non-String data

In other cases, such as when making comparisons between String and non-String data, it may be necessary or desirable to force values into String form. String.valueOf() can be used to return a String representation of some other data type value. The example below converts Last values and Size values to Strings.

t = db.t("LearnDeephaven" , "StockTrades")\
    .where("Date=`2017-08-23`")\
    .head(10)\
    .select("Timestamp", "Sym", "Last=String.valueOf(Last)", "Size=String.valueOf(Size)")

When writing where clauses or joins that will compare or match different columns in a table, it is important to be aware of the data types of the columns. If one column to be matched is a String and the other is some other data type, then use of String.valueOf() may be needed to coerce the other column into a String which can be compared. Alternatively, other data types also have their own conversion methods which can be used to convert from Strings, such as:

• Long.parseLong - creates a long from a String.
• Integer.parseInt - creates an int from a String.
• Double.parseDouble - creates a double from a String.
• .charAt() - extracts a char from a String; .charAt(0) will extract a single character from the beginning of a String into a char value - this is also how to convert a single character String into a char.

x = emptyTable(1)\
    .update("Col1=Long.parseLong(`1234`)","Col2=`Hello`.charAt(1)")

Because Col2 in the above example is a char value, executing

y = x.where("Col2 = `e`")

will fail. It will fail because e is a String, and String and char are not implicitly comparable. Instead, a query like this would be needed:

y = x.where("Col2 = 'e'")

This compares Col2 to 'e'. The straight ticks here delimit a char, rather than a String, matching the type of Col2.

Note that converting from String to some other data type may fail. If a column contains a mix of numeric and non-numeric values, for example, (such as N/A, or even embedded commas as thousands separators), then Long.parseLong will fail when it encounters the non-numeric data. For comparisons, coercing everything to Strings is safer and may be easier. For data sanitation and easier use in later reporting, it may be necessary to construct ternary expressions to address values that cannot be converted by the various parsing methods.

Dates as Strings

You may need to convert a DBDateTime to a String. Some simple examples of common use cases follow:

timeStuff = db.t("LearnDeephaven", "EODTrades")\
    .updateView("CurrentTime = currentTime()",\
    "StringDT = format(EODTimestamp, TZ_NY)",\
    "StringDate = formatDate(EODTimestamp, TZ_NY)",\
    "StringToTime = convertDateTime(StringDate + `T12:00 NY`)")

More examples

Here is a robust set of example queries you can run in Deephaven (also found in the Ultimate Table Operations cheat sheet).

stringStuff = db.t("LearnDeephaven", "EODTrades")\
     .view("StringDate = formatDate(EODTimestamp, TZ_NY)",\
     "Ticker","Close",\
     "NewString = `new_string_example_`",\
     "ConcatV1 = NewString + Ticker",\
     "ConcatV2 = NewString + `Note_backticks!!`",\
     "ConcatV3 = NewString.concat(Ticker)",\
     "ConcatV4 = NewString.concat(`Note_backticks!!`)",\
     "StartBool = Ticker.startsWith(`M`)",\
     "NoEndBool = !Ticker.endsWith(`OG`)",\
     "ContainedBool = Ticker.contains(`AA`)",\
     "NoContainBool = !Ticker.contains(`AA`)",\
     "FirstChar = Ticker.substring(0,1)",\
     "LengthString = Ticker.length()",\
     "CharIndexPos = ConcatV1.charAt(19)",\
     "SubstringEx = ConcatV1.substring(11,20)",\
     "FindIt = NewString.indexOf(`_`)",\
     "FindItMiddle = NewString.indexOf(`_`, FindIt + 1)",\
     "FindLastOf = NewString.lastIndexOf(`_`)",\
     "SplitToArrays = NewString.split(`_`)",\
     "SplitWithMax = NewString.split(`_`, 2)",\
     "SplitIndexPos = NewString.split(`_`)[1]",\
     "LowerCase = Ticker.toLowerCase()",\
     "UpperCase = NewString.toUpperCase()",\
     "DoubleToStringv1 = Close + ``",\
     "DoubleToStringv2 = String.valueOf(Close)",\
     "DoubleToStringv3 = Double.toString(Close)",\
     "StringToDoublev1 = Double.valueOf(DoubleToStringv1)")