Migrate oracle to mysql

Question

I am trying to migrate oracle to mysql.

Question 1.

Instr('aa bb cc', ' ', -1,1)

I changed it

CHAR_LENGTH('aa bb cc') - LOCATE(' ', REVERSE('aa bb cc'))+1

both return

6

But I don't know how to make query with

Instr('aa bb cc', ' ', -1, 2)

Question 2.

This is oracle query.

CASE WHEN Regexp_like(
REPLACE(SUBSTR(TRIM(col), INSTR(TRIM(col), ' ', -1,1), 
                                                        (LENGTH(TRIM(col)) - INSTR(TRIM(col), ' ',-1, 1))
 + 1), ' ',
 ''),
 '[0-9|0-9\-]') THEN
REPLACE(SUBSTR(TRIM(col), INSTR(TRIM(col), ' ', -1, 2),
 (INSTR(TRIM(col), ' ', -1, 1) - INSTR(TRIM(col), ' ', -1, 2)
) +
 1), ' ', '') END

How to change the query to mysql query?

Answer 1

MySQL also has instr() function, however it has less feature than Oracle

ora :: Instr('aa bb cc', ' ', -1, 1) == 
my  :: length('aa bb cc') - length(substring_index('aa bb cc', ' ', -1))

ora :: Instr('aa bb cc', ' ', -1, 2) == 
my  :: length('aa bb cc') - length(substring_index('aa bb cc', ' ', -2))

MySQL also uses (so there is no need to change):

CASE WHEN value THEN do-something END

MySQL replace:

REPLACE(str, from, to)
https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_replace

Other issue should be all same.

Regexp_like

https://docs.oracle.com/cd/B12037_01/server.101/b10759/conditions018.htm

regexp(source_string, pattern, match_parameter)

https://dev.mysql.com/doc/refman/5.7/en/pattern-matching.html

source_string REGEXP pattern

Answer 2

As a Research you can use INSTR in MYSQL like:

Set @col  = "sample";
select INSTR(@col,"le")

You can refer this INSTR()

Getting the length by removing spaces:

select LENGTH((replace(@col," ","")))