Oracle CHR(164) to VARCHAR(10)

Phil5780
Phil5780 used Ask the Experts™
on
I'm experienced in SQL Server but new to Oracle.  I've created a Oracle XE 11G database, table and inserted several records successfully.  But I'm unable to insert extended characters as shown below.  My column's datatype is VARCHAR(2000) but I get the error 'ORA-01722: invalid number' upon attempting to CAST CHR() to VARCHAR.  What am I missing here?

insert into tblThreadContent
(Content)
Values
('Regular text followed by an extended character, with space ' + cast(chr(164) as varchar(10)))

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Chris AshcraftSenior Analyst - Technology

Commented:
I believe you'll want to use NVARCHAR2 to store extended characters.

Btw, its recommended not to use VARCHAR - you should use VARCHAR2 instead.
Most Valuable Expert 2011
Top Expert 2012
Commented:
the problem is you are trying add two strings


 +   is the concatenation operator in sql server

|| is the concatenation operator in Oracle


so the error is actually coming from trying to implicitly convert the first string into a number.
The cast on chr(164) is succeeding, but it's not necessary.



you don't need the cast at all, in fact, it won't help anyway since you're only appending a single character, not 10

insert into tblThreadContent
(Content)
Values
('Regular text followed by an extended character, with space ' || chr(164))

also, in oracle you should use varchar2 rather than varchar.

Author

Commented:
Pow!! Right on!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial