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
```
Vertica not support non-literal position parameter too.
```
dbadmin=> SELECT regexp_replace('healthy, wealthy, and wise', '\\w+thy',
'something', 1)
dbadmin-> ;
regexp_replace
----------------------------
healthy, wealthy, and wise
(1 row)
dbadmin=> SELECT regexp_replace('healthy, wealthy, and wise', '\\w+thy',
'something', select 1)
dbadmin-> ;
ERROR 4856: Syntax error at or near "select" at character 77
LINE 1: ...althy, wealthy, and wise', '\\w+thy', 'something', select 1)
^
dbadmin=> select version();
version
------------------------------------
Vertica Analytic Database v9.1.1-0
(1 row)
```
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
```
3.does other DBs support position parameter in all regex like functions?
Oracle:
`Like` and `REGEXP_LIKE` not support position parameter.
Oracle do not support `StringSplit`, `RegExpExtract`.
vertica:
`LIKE-predicate` and `REGEXP_LIKE` not support position parameter.
vertica do not support `StringSplit`, `RegExpExtract`.
redshift:
redshift do not support `Like`, `REGEXP_LIKE`, `StringSplit`,
`RegExpExtract`.
----------------------------------------------------------------
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:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
