sql - substring()在配置单元中返回空白

我想从列的字段中提取最后10个数字，所以我正在使用 hive 中内置的子字符串。但是，如果字段值小于定义的值(输入:orig_number1409765260121409804345121740016174001617250460171我正在使用此代码。select *,length(orig_number) as leng,substr(orig_number,-10) as subbstring from num_table sort by orig_number;输出为:
orig_number leng subbstring140976526012 12 0976526012140980434512 12 1409804345121740016 7
1740016 7
17250460171 11 725046017

最佳答案

从行尾最多检索10个字符

select  orig_number
       ,regexp_extract (orig_number,'.{1,10}$',0)   as orig_number_suffix

from    num_table
;

+--------------+--------------------+
| orig_number  | orig_number_suffix |
+--------------+--------------------+
| 140976526012 |         0976526012 |
| 140980434512 |         0980434512 |
|      1740016 |            1740016 |
|      1740016 |            1740016 |
|  17250460171 |         7250460171 |
+--------------+--------------------+

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF
https://docs.oracle.com/javase/7/docs/api/java/util/regex/Pattern.html

这就是为什么你空白的原因。
在我看来， substr 函数的设计很糟糕。

UDFSubstr.java

private int[] makeIndex(int pos, int len, int inputLen) {
    if ((Math.abs(pos) > inputLen)) {
      return null;
    }

...

关于sql - substring()在配置单元中返回空白，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/42365352/