String

Q:
Displayed as XX | XXXXXXXXXXX.
Is there a way to only get a value from a specific column?
For example, how do I get the value from column 2?
A:
Extract the part of the value you need, e.g. from after the ‘|’ to the end of the string:
strextract(this.text,’|’)


I have also extended the database engine with SET STRCOMPARE ON.
This causes strings to be compared case insensitive and EXACT comparisons. The strings are padded with spaces up to the maximum length of either string. This resolves the issue of SQL returning multiple rows when looking up a substring of a column value. This behavior is like MSSQL now. You should only set it on when you want this behavior as it affects all string comparisons so:
“hello” = “HELLO”
will be true if STRCOMPARE is ON
.

Index key comparisons are case insensitive too now so the optimizer will handle mixed case comparisons.

One thing worth mentioning also is that in Lianja the
== operator behaves like EXACT only when the left hand side operand and the right hand side operand of the same length
otherwise == is a “pattern matching” operator. e.g.
select * from customers where customerid==”A*”
Will return all customers that start with “A”.

The pattern matching
operators _ * ? % [chars] and ^[chars] can be specified in the pattern.

Also remember that you can also
SET SOFTSEEK ON to progressively lookup a key by chopping characters off the end until a match is found.


In Lianja scripting, Javascript, PHP, Python, C, C++, Java, etc comparing strings is case sensitive.
If you are storing data then that’s up to your App to make sure it goes in in the correct case particularly when indexes are being updated or populated.

“hello” != “HELLO”

So fix up your data and use input masks to guarantee what is typed in by a user is correctly transformed before it is stored in a database.



 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s