Thanks for the question, Subrat.
Asked: March 06, 2020 - 10:58 am UTC
Last updated: March 06, 2020 - 3:49 pm UTC
Version: 12.1
Viewed 1000+ times
You Asked
Trying to figure out the proper usage scenarios for CHAR String Function and BYTE String Function.
I have a custom table with a description column (240 CHAR) and a standard table with description column (240 BYTE). The issue happened is when we get a description larger than 240, we trim it using SUBSTR and insert it into custom table (which is fine) then that same value gets inserted to standard table.
This process works fine unless we hit a multibyte character then the SUBSTR('description',0,240) cant be inserted into the standard table as its size becomes more than 240 bytes.
I understand that 1 CHAR is not equal to 1 BYTE. And, we solved the above issue using SUBSTRB instead of SUBSTR.
SUBSTR gives us the result in no of characters and SUBSTRB gives us the result in no of bytes.
My question here is :
What are the scenarios where we should use SUBSTR/LENGTH and SUBSTRB/LENGTHB?
Can we always use SUBSTRB in our code every time as we dont know when we might hit a multibyte character?
Thanks,
Subrat
with LiveSQL Test Case:
and Chris said...
To answer that, you need to understand why you're substringing.
Using your examples:
240 CHAR col => use SUBSTR
240 BYTE col => use SUBSTRB
It's more common to use the character version of these functions as this is the more common use-case, e.g.:
* Trim to display the first N characters of a string; customers can get confused when different numbers of characters are trimmed
* Processing fixed-position files, where you need to get the characters at position N ... M.
But if you're storing values in byte-limited columns/variables, you need to use the binary versions.
So, in answer to:
Can we always use SUBSTRB in our code every time as we dont know when we might hit a multibyte character?
No.
You have to think about what your expected output is. And use the appropriate substr/length function based on this requirement.
Is this answer out of date? If it is, please let us know via a Comment