For a same String, will SQLite's length will ever return a different value than Java's length method?

user_72437 0
Viewed 465

Give a same String data

  1. SQLite perform length calculation on its TEXT column.
  2. The TEXT column is read into (Using Android Room database) Java String, then Java performs String.length()

Is there any chance that these yields 2 different value?

I have do a rough test using English and non-English characters. Both yields the same value.

But, I am not sure whether there is any edge cases I have missed out?

string-length android-sqlite sqlite android java
3 Answers

Since you are looking for edge cases...

From SQLite's Built-In Scalar SQL Functions:

length(X)
For a string value X,
the length(X) function returns the number of characters (not bytes) in X
prior to the first NUL character. (emphasis mine)
Since SQLite strings do not normally contain NUL characters,
the length(X) function will usually return the total number of characters in the string X....

So, SQLite, for:

SELECT LENGTH('a' || CHAR(0) || 'b')

will return 1,

but Java, for:

String s = "a" + Character.toString('\0') + "b";
System.out.println("" + s.length());

will return 3.

user_104988280

There could be some cases where the length differ, Java uses UTF-16 for internal string representation, so some kind of characters will need a surrogate pair to be stored in memory. Java's String.length() does not take into account this.

A simple example using the emoji character

    class HelloWorld {
    public static void main(String[] args) {
        System.out.println("".length());
    }}

This will print 2.

On the other hand the documentation of sqlite states:

For a string value X, the length(X) function returns the number of characters (not bytes) in X prior to the first NUL character.

It specifies that it counts the characters

sqlite> select length('');

this will return 1.

This is not exclusive to "emojis" it will be the same also for some languages that have characters with "high" codepoints like some Asian characters

tested with sqlite 3.28.0 and openjdk version "1.8.0_252". I think it should hold true for your stack.

user_115091010

Acording to the documentation of Sqlite NUL characters (ASCII 0x00, Unicode \u0000) in the TEXT field can lead to different length values.

Taking for example the text Hello\u0000World

Sqlite will return a length of 16

Java will return a length of 11

Java will count the NUL character as 1 while Sqlite will count 6. The same text will have different values.

user_143278710
Related