[GitHub] [spark] beliefer edited a comment on pull request #29891: [SPARK-30796][SQL] Add parameter position for REGEXP_REPLACE

[GitBox <gi...@apache.org>]

beliefer edited a comment on pull request #29891:
URL: https://github.com/apache/spark/pull/29891#issuecomment-707636820


   1.does other DBs support non-literal position parameter?
   Oracle not support non-literal position parameter
   ```
   WITH strings AS ( SELECT 'healthy, wealthy, and wise' s FROM dual ) SELECT
   s "STRING",
   regexp_replace( s, '\w', 'something', 30 ) "MODIFIED_STRING" 
   FROM
   	strings;
   ```
   result is:
   STRING | MODIFIED_STRING
   -- | --
   healthy, wealthy, and wise | healthy, wealthy, and wise
   
   ```
   WITH strings AS ( SELECT 'healthy, wealthy, and wise' s FROM dual ) SELECT
   s "STRING",
   regexp_replace( s, '\w', 'something', select 30 from dual ) "MODIFIED_STRING" 
   FROM
   	strings;
   ```
   result is:
   ```
   WITH strings AS ( SELECT 'healthy, wealthy, and wise' s FROM dual ) SELECT
   s "STRING",
   regexp_replace( s, '\w', 'something', select 30 from dual ) "MODIFIED_STRING" 
   FROM
   	strings
   > ORA-00936: missing expression
     
   > Time: 0.03s
   ```
   2.does other DBs support negative position value? Seems Redshift supports it
   Redshift tell user position is a positive integer.
   Oracle:
   ```
   WITH strings AS ( SELECT 'healthy, wealthy, and wise' s FROM dual ) SELECT
   s "STRING",
   regexp_replace( s, '\w', 'something', 0 ) "MODIFIED_STRING" 
   FROM
   	strings
   > ORA-01428: argument '0' is out of range
     
   > Time: 0.032s
   ```
   ```
   WITH strings AS ( SELECT 'healthy, wealthy, and wise' s FROM dual ) SELECT
   s "STRING",
   regexp_replace( s, '\w', 'something', -1 ) "MODIFIED_STRING" 
   FROM
   	strings
   > ORA-01428: argument '-1' is out of range
     
   > Time: 0.031s
   ```
   


----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org