Home » SQL & PL/SQL » SQL & PL/SQL » length & replace
length & replace [message #208211] Fri, 08 December 2006 11:00 Go to next message
ashish_pass1
Messages: 114
Registered: August 2006
Location: delhi
Senior Member
hello team

i had a query to get no. of "O" through comment column from table author. but i unable to get reqd. result.
can u please help me to solve it?

table is


ashish>select * from author;

AUTHORNAME                     COMMENTS
------------------------------ ---------------------------------------------------------
DIETRICH BONHOEFFER            GERMAN THEOLOGIAN, KILLED IN A WAR CAMP
ROBERT BRETALL                 KIERKEGAARD ANTHOLOGIST
ALEXANDRA DAY                  AUTHOR OF PICTURE BOOKS FOR CHILDREN
STEPHEN JAY GOULD              SCIENCE COLUMNIST, HARVARD PROFESSOR
SOREN KIERKRGAARD              DANISH PHILOSOPHER AND THEOLOGIAN
HARPER LEE                     AMERICAN NOVELIST, PUBLISHED ONLY ONE NOVEL
LUCY MAUD MONTGOMERY           CANADIAN NOVELIST
JOHN ALLEN PAULOS              MATHEMATICS PROFESSOR
J.RODALE                       ORGANIC GARENING EXPERT

9 rows selected.



my query is-

ashish>ed
Wrote file afiedt.buf

1* select (length('comment')-length(replace('comment', 'O', ''))) "comment" from author
ashish>/

i m getting following results ---


comment
----------
0
0
0
0
0
0
0
0
0

9 rows selected.


but my reqd result is like

comment
-------
2
2
5
3
4
4
1
2
1


with regards
ashish
Re: length & replace [message #208219 is a reply to message #208211] Fri, 08 December 2006 12:08 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Don't use the single quotes around comment:

select (length(comment)-length(replace(comment, 'O', ''))) "comment" from author;
Re: length & replace [message #208279 is a reply to message #208211] Sat, 09 December 2006 00:59 Go to previous messageGo to next message
ashish_pass1
Messages: 114
Registered: August 2006
Location: delhi
Senior Member
hello ebrian

i tried ..but unable to get the results.
can u suggest me more.

what i tried is--
ashish>select (length(comments)-length(replace(comments, 'O', ' '))) from author;

LENGTH(COMMENTS)-LENGTH(REPLACE(COMMENTS,'O',''))
-------------------------------------------------
0
0
0
0
0
0
0
0
0

9 rows selected.

not getting the reqd. result.

please look after my query.
where i m doing mistake.

with regards
ashish
Re: length & replace [message #208294 is a reply to message #208279] Sat, 09 December 2006 02:55 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
The posted query should work. Try to show the lengths separately:
select length(comment) "length_full", 
  length(replace(comment, 'O', '')) "length_without_O",
  (length(comment)-length(replace(comment, 'O', ''))) "comment"
from author;

If the character is really 'O' (big o), not '0' (zero), it is possible that comment has char type (not varchar2). Try
select data_type from user_tab_columns
where table_name = upper( 'author' ) and column_name = upper('comment');

Try trim(column) instead of column in the query. I hope it should help.
Re: length & replace [message #208295 is a reply to message #208279] Sat, 09 December 2006 03:04 Go to previous messageGo to next message
ab_trivedi
Messages: 460
Registered: August 2006
Location: Pune, India
Senior Member
hi ashish,

How are you ? In the query you are giving space in single quotes that is why your result is not coming .

bye
Ashu
Re: length & replace [message #208321 is a reply to message #208295] Sat, 09 December 2006 08:51 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
ab_trivedi is correct, it appears that you are including a space between the single quotes. To prevent that from occurring, you can actually just remove the quotes all together and just use:

select (length(comment)-length(replace(comment, 'O'))) "comment" from author;
Re: length & replace [message #208344 is a reply to message #208211] Sat, 09 December 2006 11:44 Go to previous messageGo to next message
ashish_pass1
Messages: 114
Registered: August 2006
Location: delhi
Senior Member
hello ab_trivedi

yes u r right. i m using space between single quote.
ok, i will give u my feedback after solving it.

thanxx to u ebrian also

with regards


[Updated on: Sat, 09 December 2006 11:44]

Report message to a moderator

Re: length & replace [message #208384 is a reply to message #208211] Sun, 10 December 2006 03:55 Go to previous message
ashish_pass1
Messages: 114
Registered: August 2006
Location: delhi
Senior Member
thanx to u all,

ur given query works well.
i used space between the single quotes.
thanx for the help .

with regards
ashish
Previous Topic: Object in table
Next Topic: SQL UPDATE Statement Problem
Goto Forum:
  


Current Time: Sat Dec 10 14:43:33 CST 2016

Total time taken to generate the page: 0.08314 seconds