Shulou(Shulou.com)06/01 Report--

How are length, lengthb, and substr used in oracle? I believe that many inexperienced people are at a loss about this, so this article summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

In oracle, the more common are length and substr, at least most of what I see are these two. If I hadn't read the code yesterday and found lengthb and substrb, I would have forgotten. Length represents the character length of the string, lengthb represents the byte length of the string, substr indicates that the substring is obtained based on the character length, and substrb indicates that the string is obtained according to the byte length. Let's look directly at the example to illustrate:

SELECT length ('Ye Dehua abc')-length in terms of characters, Chinese characters, English and numbers are all one character, so 6 FROM dual; SELECT lengthb ('Ye Dehua abc')-length is returned here in terms of bytes. I have UTF-8 code here, 3 bytes of Chinese characters and one byte of English, so 12 FROM dual is returned here. SELECT substr ('Ye Dehua abc',-- substr intercepts by character, intercepts to a, returns: Ye Dehua a 1,4) FROM dual; SELECT substrb ('Ye Dehua abc', 1,2)-substrb intercepts by byte, 2 less than one Chinese character length, returns: two spaces FROM dual SELECT substrb ('Ye Dehua abc', 1,3)-substrb is truncated by byte. 3 is exactly the length of a Chinese character. Return: Ye FROM dual; SELECT substrb ('Ye Dehua abc', 1,4)-substrb truncated by byte, 4 more than one Chinese character less than two Chinese characters, return: Ye plus a space FROM dual

There should be other similar methods in oracle, which are not summarized here, and that's probably what it means. To add, it is said that when defining character types in oracle, the default length is byte. For example, varchar2 (20) represents a length of 20 bytes, and if you want to define it as a character, then varchar2 (20 char), but generally we also define it in bytes.

After reading the above, have you mastered how length, lengthb and substr are used in oracle? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.