How to insert regex with special characters like “&,?.@:;” in oracle database?

Question

I have a regex value that I want to insert in oracle database table column but I have some problems that the value doesn't inserted correctly in the database this is the value that I want to insert :

INSERT INTO valid_value VALUES (9, 14, 'REGEX[[A-Z.,-\s]+(,\s[A-Za-z.,-\s]+)?]', 1);

the result of this insert is :

valid_value 
+--------------------------------------+
REGEX[[A-Z.,-\s]+(,\s[A-Za-z.,-\s]+)]

I lose the "?" character can some one help me on this how to insert regex value in a table column.

I start the script using a batch file

set DB_CREATE_ROOT="%~dp0"
set SQL_INIT_CONF_DIR=%DB_CREATE_ROOT%\Scripts\configuration\
for /r %SQL_INIT_CONF_DIR% %%F in (*init.sql) do (


    ECHO %DATE% %TIME%  "%%F" >> %LOG_NAME%
    sqlplus -L Test_APP/welkom@//localhost:1521/xe @"%%F" >> %LOG_NAME% 2>&1
)

thanks in advance

Works fine for me, in SQL\*Plus and SQL Developer. Which client are you using? It woulds like maybe the client is interpreting the question mark? — Alex Poole, Jan 12 '18 at 10:49
None of those characters have a special meaning in the context of SQL, as a simple `SELECT 'REGEX[[A-Z.,-\s]+(,\s[A-Za-z.,-\s]+)?]' FROM DUAL;` illustrates. What exact problem are you facing? — Álvaro González, Jan 12 '18 at 10:50
it's work fine in sqldevlopper but when I run the script using sqlplus it's not work — e2rabi, Jan 12 '18 at 10:50
It works for me in SQL*Plus. How **exactly** is it not working? — Álvaro González, Jan 12 '18 at 10:50
it insert this value REGEX[[A-Z.,-\s]+(,\s[A-Za-z.,-\s]+)] instead of REGEX[[A-Z.,-\s]+(,\s[A-Za-z.,-\s]+)?] I lose *?* character — e2rabi, Jan 12 '18 at 10:53
What kind of script - just a .sql you run from SQL\*Plus? And how are you querying it? What happens if you just run that insert manually directly from the SQL\*Plus prompt, and then query the table the same way? — Alex Poole, Jan 12 '18 at 10:57
Can you please edit the question and copy+past the exact SQL*Plus session, including both `INSERT` and a subsequent `SELECT`? — Álvaro González, Jan 12 '18 at 11:05
I think it won't fail if you just execute the statement alone. You have a complex data load process and I don't think there's enough information to determine that `?` alone is the point of failure. I suggest you grab a copy of everything and start removing steps until the issue gets fixed or you have a small self-contained example to share. BTW, here's a little [fiddle](http://sqlfiddle.com/#!4/283d2/1); — Álvaro González, Jan 12 '18 at 11:17
I don't see how to reproduce what you are seeing; maybe there's something in your environment or in the .sql script that causing this, but nothing obvious jumps out. (Having `set define ?` won't make a difference here, for instance.) — Alex Poole, Jan 12 '18 at 11:44
it's work when I use the chr() function with ascii code for "?" thanks guys — e2rabi, Jan 12 '18 at 11:47

score 3 · Accepted Answer · edited Jan 12 '18 at 14:45

3

You can use CHR function for each of the special characters if you find difficulties with direct inserts.

Thanks

edited Jan 12 '18 at 14:45

Kaushik Nayak

30,772
5
32
45

answered Jan 12 '18 at 11:41

Chittaranjan Dalai

58
1
5

Yes the chr solve the problem it's work with INSERT INTO valid_value VALUES (9, 14, 'REGEX[[A-Z-\s]+(,\s[A-Za-z-\s]+)'||chr(63)||']', 1); thx – e2rabi Jan 12 '18 at 11:42
Welcome to S.O! – Kaushik Nayak Jan 12 '18 at 14:46

Álvaro González · Answer 2 · 2018-01-12T17:36:57.713

2

For the records, none of the characters mentioned have a special meaning inside SQL strings thus they don't need any special treatment:

SQL> SELECT '&,?.@:;' AS are_we_special
  2  FROM DUAL;

ARE_WE_
-------
&,?.@:;

(online demo)

... being & the only possible exception (“SET DEFINE OFF” in Oracle Database) and only in the context of SQL*Plus and SQL Developer—in which case you'd be getting a Enter value for xxxxx prompt.

Whatever problem the OP had, CHR() is just a workaround for his specific undisclosed issue.

edited Jan 12 '18 at 17:36

answered Jan 12 '18 at 11:58

Álvaro González

142,137
41
261
360

1

Not in SQL; but `&` is the default substitution character in those clients. Trivial to avoid that one being a problem with `set deifne off`, of course, and doesn't matter anyway when followed by punctuation as in your demo. (And `set define ?` wouldn't cause the OP's problem for the same reason.) – Alex Poole Jan 12 '18 at 12:20
1

@AlexPoole I've tried to improve my answer. I don't have Oracle here to verify stuff so I hope I didn't make any mistake. – Álvaro González Jan 12 '18 at 17:37

score 0 · Answer 3 · answered Jan 12 '18 at 10:46

0

This may help you

   INSERT INTO valid_value VALUES (9, 14, 'REGEX[[A-Z.,-\s]+(,\s[A-Za-z.,-\s]+)'?']', 1);

answered Jan 12 '18 at 10:46

Bibin Mathew

455
3
11

This throws "ORA-00911: invalid character" in SQL\*Plus, and SQL Developer seems to think the question mark is a bind placeholder and errors with "Missing IN or OUT parameter". – Alex Poole Jan 12 '18 at 15:24

XING · Answer 4 · 2018-01-12T11:00:10.103

0

Try this:

INSERT INTO valid_value VALUES (9, 14, 'REGEX[[A-Z.,-\s]+(,\s[A-Za-z.,-\s]+) ?]', 1);

See demo:

In SQLPLUS:

SQL> /

COL1                                            
---------------------------------------------
REGEX[[A-Z.,-\s]+(,\s[A-Za-z.,-\s]+) ?]

Now question is which client you are using. In SQLPLUS, its working as shown above.

edited Jan 12 '18 at 11:00

answered Jan 12 '18 at 10:54

XING

9,608
4
22
38

This insert this value "REGEX[[A-Z.,-\s]+(,\s[A-Za-z.,-\s]+)\]" – e2rabi Jan 12 '18 at 10:58
Why would this make a difference to a string literal (other than modifying the intended value)? The regex isn't being applied to anything yet? – Alex Poole Jan 12 '18 at 11:00
Just I want to store the regex as a string value the problem is each time lose the "?" character – e2rabi Jan 12 '18 at 11:01
@ErrabiAyoub Did you use what i posted. I mean i put a space before `?` . Its not the same as in your question – XING Jan 12 '18 at 11:02
I will try to add space – e2rabi Jan 12 '18 at 11:03
@ErrabiAyoub You just copy and paste and execute what i posted, It should work for you, – XING Jan 12 '18 at 11:03
Since when do spaces have any special meaning in SQL? It shouldn't make any difference from Oracle perspective and it'll break the regexp. – Álvaro González Jan 12 '18 at 11:04
@ÁlvaroGonzález In `Oracle` Special characters either needed to be escaped or should be with a space if you want to store it. You can try the demo posted. I hope regex will also not be broken as it wont consider the space. See and try the demo i posted and let me know if its not working for you – XING Jan 12 '18 at 11:08
Even accepting that `?` is a special character, if you add a space the space gets stored. Try `SELECT DUMP('?'), DUMP(' ?') FROM DUAL;`. – Álvaro González Jan 12 '18 at 11:10
still ignore ? even I add space the result of SELECT DUMP('?'), DUMP(' ?') FROM DUAL is : Typ=96 Len=1: 63 Typ=96 Len=2: 32,63 – e2rabi Jan 12 '18 at 11:12
@ÁlvaroGonzález yes,,i knw space would be there but i really wanted to see if this works with OP since i dont know the client he is using plus simply executing `REGEX[[A-Z.,-\s]+(,\s[A-Za-z.,-\s]+) ?]` in oracle will throw error – XING Jan 12 '18 at 11:13
it's work when I use the chr() function with ascii code for "?" thanks guys – e2rabi Jan 12 '18 at 11:43

How to insert regex with special characters like “&,?.@:;” in oracle database?

4 Answers4