Second substring in Google Big Query

Question

I am trying to find the index of second occurence of a substring in a string using Google Big Query.

For example, in string 'challcha', second occurence of 'ch' would be at position 6.

I understand that this can be achieved using CharIndex in Oracle. I am trying to achieve this in Google Big Query.

Any help is appreciated !!

Answer 1

For BigQuery with pure SQL String functions

SELECT test, 
  INSTR(test, 'ch') + 1 + INSTR(SUBSTR(test, INSTR(test, 'ch') + 2), 'ch') AS pos,
FROM 
  (SELECT 'challcha' AS test),
  (SELECT 'chcha' AS test),
  (SELECT 'chha' AS test)
WHERE 
  INSTR(SUBSTR(test, INSTR(test, 'ch') + 2), 'ch') > 0

Note: INSTR is case-sensitive so you might want to put all in LOWER or UPPER if you have mixed cases

With BigQuery User-Defined Functions

SELECT test, pos FROM JS(
(
  SELECT test FROM 
    (SELECT 'challcha' AS test),
    (SELECT 'chcha' AS test),
    (SELECT 'chha' AS test)
) ,
test,
"[{name: 'test', type:'string'},
  {name: 'pos', type:'integer'}
  ]
",
"function(r, emit) {
  var search = 'ch';
  var pos1 = r.test.indexOf(search) + 1;
  var pos2 = r.test.indexOf(search, pos1) + 1;
  if (pos1 * pos2 == 0) pos2 = 0
  emit({test: r.test, pos: pos2});
}"
)

With pure BigQuery Regular expression functions

SELECT test, 
  LENGTH(REGEXP_EXTRACT(test, r'(?i)(.*?)ch')) + 3 + 
    LENGTH(REGEXP_EXTRACT(REGEXP_EXTRACT(test, r'(?i)ch(.*)'), r'(?i)(.*?)ch')) AS len,
FROM 
  (SELECT 'ChallCha' AS test),
  (SELECT 'abChallCha' AS test),
  (SELECT 'chcha' AS test),
  (SELECT 'chha' AS test)