1

I want to know the position of the data which doesn't start with 0. Like below example, in first row first digits are 00 and it returns the 3 position as non zero values start from 3 position

member_id          values

008507743          3     
017521344          2    
040324598          2

SQL CODE:- 

select  member_id,patindex('%[^0]%',MEMBER_ID)as Index_value  from tablename

The above thing in sql and I just want the same in hive and unable to find out the solution for the same. Any help would be appreciable. Thanks

Wiktor Stribiżew
  • 607,720
  • 39
  • 448
  • 563
Gurpreet Singh
  • 197
  • 3
  • 6
  • 14

1 Answers1

2

Try this

SELECT INSTR(member_id, regexp_extract(member_id, '[^0]', 0)) from tablename

From documentation

instr(string str, string substr) :- Returns the position of the first occurrence of substr in str. Returns null if either of the arguments are null and returns 0 if substr could not be found in str. Be aware that this is not zero based. The first character in str has index 1.

Explanation

regexp_extract returns the first number/alphabet that is not 0.

instr returns the index of the found element from regexp_extract

rock321987
  • 10,942
  • 1
  • 30
  • 43