OraFAQ Forum: SQL & PL/SQL » chr(10) function

Home » SQL & PL/SQL » SQL & PL/SQL » chr(10) function

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

chr(10) function [message #208386]

Sun, 10 December 2006 04:31

ashish_pass1
Messages: 114
Registered: August 2006
Location: delhi

Senior Member

hello team

please go through my query & point out what i will i do to make it correct?

i have query like this-


SQL> ed
Wrote file afiedt.buf

  1   select rpad(substr(authorname, instr(authorname, ' ', 1)), 20)
  2  ||chr(10)||
  3   substr(authorname, 1, instr(authorname, ' ', -1))
  4  ||chr(10)||
  5  length(authorname)-length(replace(authorname, 'T', '')) "sense"
  6*  from author
SQL> /
||chr(10)||
          *
ERROR at line 4:
ORA-01722: invalid number

though after few modification on my query it runs well.


SQL> select length(authorname)-length(replace(authorname, 'T', ''))
  2  ||chr(10)||
  3  rpad(substr(authorname, instr(authorname, ' ', 1)), 20)
  4  ||chr(10)||
  5   substr(authorname, 1, instr(authorname, ' ', -1))"sense"
  6   from author;

please advice me why i unable to run the first query??

thanxx

with regards
ashish

Report message to a moderator

Re: chr(10) function [message #208389 is a reply to message #208386]

Sun, 10 December 2006 04:42

Littlefoot
Messages: 21825
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Because

length(authorname)-length(replace(authorname, 'T', ''))

is a number; you can't concatenate a string with a number. To make it work, add TO_CHAR function:

TO_CHAR(ength(authorname)-length(replace(authorname, 'T', '')))

Report message to a moderator

Re: chr(10) function [message #208390 is a reply to message #208386]

Sun, 10 December 2006 04:48

dba_blr
Messages: 43
Registered: December 2006

Member

Use this...

select rpad(substr(authorname, instr(authorname, ' ', 1)), 20)
||chr(10)||
substr(authorname, 1, instr(authorname, ' ', -1))
||chr(10)||
to_char(length(authorname)-length(replace(authorname, 'T', ''))) "sense"
from author;

Hope it works for you...

Report message to a moderator

Re: chr(10) function [message #208396 is a reply to message #208386]

Sun, 10 December 2006 05:10

ashish_pass1
Messages: 114
Registered: August 2006
Location: delhi

Senior Member

thanxx to all

u r right, i m concate number to character.

but i m concate character to number which runs well.
(second query works, it dnt shows any error)

please advice me more.

with regards

ashish

Report message to a moderator

Re: chr(10) function [message #208401 is a reply to message #208396]

Sun, 10 December 2006 07:06

dba_blr
Messages: 43
Registered: December 2006

Member

Go through Oracle SQL Reference guide for complete details.

Report message to a moderator

Re: chr(10) function [message #208421 is a reply to message #208401]

Sun, 10 December 2006 15:48

Littlefoot
Messages: 21825
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

I'd like to correct myself.

No, it is not about TO_CHAR function. Oracle is clever enough to perform an implicit conversion between datatypes if possible.

It is about arithmetic.

How come? Check this example: first, concatenate a character and a number. Oracle will implicitly convert 3 (a number) into '3' (a character) and return the correct value: 'a3':

SQL> select 'a' || length('abc') from dual;

'A
--
a3

Now let's try something like you did: concatenate a string with "length - length". If we calculate it, shortly it would look like this:

SELECT 'a' || 3 - 2 FROM dual:

SQL> select 'a' || length('abc') - length('de') from dual;
select 'a' || length('abc') - length('de') from dual
            *
ERROR at line 1:
ORA-01722: invalid number

Oracle was confused: first part is exactly the same as previous example, but what is that "minus" sign doing here? Am I concatenating or subtracting? I have no idea - I'll return an "INVALID NUMBER" error. So, make up your mind and tell me exactly what to do!

But, if we enclose "length - length" into brackets, we'll again simplify it to

SELECT 'a' || (3 - 2) FROM DUAL =
SELECT 'a' || 1 FROM DUAL -> again our first example. Oracle knows how to deal with it, so it correctly returns the answer:

SQL> select 'a' || (length('abc') - length('de')) from dual;

'A
--
a1

Finally, example which shows a "working" case although there's subtraction and concatenation: Oracle first performs subtraction, gets a result (number 1), converts it into a character to be able to concatenate it with a character:
SELECT 3 - 2 || 'a' FROM DUAL =
SELECT 1 || 'a' FROM DUAL -> again our first, known case

SQL> select length('abc') - length('de') || 'a' from dual;

LE
--
1a

I'm sorry if my first, quite stupid answer, lead you to a wrong conclusion.

[Updated on: Sun, 10 December 2006 15:53]

Report message to a moderator

Re: chr(10) function [message #208424 is a reply to message #208386]

Sun, 10 December 2006 16:52

ashish_pass1
Messages: 114
Registered: August 2006
Location: delhi

Senior Member

thanxx littlefoot

ur great Cool

u gave me time to think about it.
ur not right . i m very -very impressed with ur answer
& appreciate ur answer. u respond me calmly, thanxx for it.

now i understand about the logic behind it.
what ever doubt was in my mind is clear now.

thanxx

with regards
ashish

Report message to a moderator

Previous Topic:	Aditional Support for PL/SQL or Java batche programs on RDBMS server
Next Topic:	pl/sql

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Aug 10 04:33:36 CDT 2025