Home » RDBMS Server » Server Administration » Special characters in SQL insert & select stmt.
Special characters in SQL insert & select stmt. [message #375168] Thu, 02 August 2001 02:13 Go to next message
mohan
Messages: 16
Registered: August 2001
Junior Member
Hi,
I need to insert special characters like ( ' & % ) into a varchar2 field. I also need to search for the field to consist of these special characters (using the like clause of the select statement). Please do help me.

Thank you for your time, effort and knowledge.

Report message to a moderator

Re: Special characters in SQL insert & select stmt. [message #375170 is a reply to message #375168] Thu, 02 August 2001 02:40 Go to previous messageGo to next message
Norvin
Messages: 22
Registered: July 2001
Junior Member
hi,

try to use concat symbol '||' and instr function
this might help
ex:

for prob 1)
select 'NOR' || '&' || 'VIN' FROM DUAL;

for prob 2)
select * from table
where instr( field,'%' ) > 0;

field - the field you want to check if contain a symbol like '%';

Report message to a moderator

Re: Special characters in SQL insert & select stmt. [message #375182 is a reply to message #375168] Thu, 02 August 2001 15:49 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
select column_name from cols
where column_name like '%\_%' escape '\';

SQL> set escape '/'
SQL> insert into t1(col1) values('valuewith/&')
SQL> select * from t1;
COL1
------
valuewith&

OR
set scan off

OR
change the variable substitution character to something else e.g.
set scan on
set define '~'

-- % = chr(37)
-- _ = chr(95)

SELECT * FROM table_name
WHERE INSTR(column_name,CHR(37)) > 0;

SELECT * FROM table_name
WHERE INSTR(column_name,CHR(95) > 0;

Report message to a moderator

Previous Topic: Sequence...
Next Topic: Re: How to copy records between 2 instances?
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Dec 24 07:16:25 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.