hive instrt_oracle & hive- substr() & instr()

1. substr()

syntax:

SUBSTR (string, start_position, [length_of_substring])

eg:

substr('This is a test', 6, 2) would return 'is'

remarks:

The original string is assumed to start at position one (1).

If substring_length is omitted, then Oracle returns all characters to the end of char.

If substring_length is less than 1, then Oracle returns null.

2. instr()

syntax:

instr(string,substring,[position],[occurrence])

syntax in hive:

instr(string,substring)

eg:

select instr('corporate floor','or',3,2) from dual; would return '14'

remarks:

The default values of both position and occurrence are 1, meaning Oracle begins searching at the first character of string for the first occurrence of substring.

The return value is relative to the beginning of string, regardless of the value of position.

我的用例 oracle:

CASE UPPER(T1.COLUMN_TYPE)

WHEN SUBSTR(RF1.PARAM_VALUE,1,INSTR(RF1.PARAM_VALUE,'~')-1) THEN SUBSTR(RF1.PARAM_VALUE,INSTR(RF1.PARAM_VALUE,'~')+1)

ELSE T1.PRODUCT_TYPE_DESC

END AS PRODUCT_TYPE

COLUMN_TYPE: AN APPLE

PARAM_VALUE: AN APPLE~FRUIT

SUBSTR(RF1.PARAM_VALUE,1,INSTR(RF1.PARAM_VALUE,'~')-1): AN APPLE

INSTR(RF1.PARAM_VALUE,'~')+1: 10

这里用于字段参数化，从一个参数表的某个相应字段中对比取值，对比~前的值，如果一致，那么取~后的值。