Home » Other » General » count of characters without space for a name (oracle 10g)
icon5.gif  count of characters without space for a name [message #466885] Wed, 21 July 2010 10:58 Go to next message
raja_beginner
Messages: 20
Registered: July 2010
Location: india
Junior Member
Hi,
I am a beginner in SQL.I has a doubt/query which I thought you guys can solve it.

Query:- Count the no. of characters with out considering spaces for each name.

For this I DId:-
SELECT LENGTH('RAJA SEKHAR') FROM DUAL;
Result:- 11
--------------------
SELECT SUBSTR('RAJA SEKHAR',1,INSTR('RAJA SEKHAR',' ')-1)
|| SUBSTR('RAJA SEKHAR',INSTR('RAJA SEKHAR',' ')+1,LENGTH('RAJA SEKHAR')) FROM DUAL;

Result:-RAJASEKHAR
--------------------
Got Required Result:-
SELECT LENGTH(SUBSTR('RAJA SEKHAR',1,INSTR('RAJA SEKHAR',' ')-1)
|| SUBSTR('RAJA SEKHAR',INSTR('RAJA SEKHAR',' ')+1,LENGTH('RAJA SEKHAR'))) FROM DUAL;

Result:- 10
---------------------

I got what I want for this name.

If the name used in the statement has got more than one space,it isn't working.
Ex:- RA JA SEKHA R

How to work on this?

My doubts are 1) Is this query correct.
* PLEASE GIVE ME GUIDELINES FOR HOW TO MAKE IT WORK FOR ANY KIND OF WORD( I MEAN WITH ANY NO.OF SPACES/with one space) AND ALSO GIVE ME THE IDEA WITH IN SQL,BECAUSE IAM NOT COMPLETED LEARNING SQL YET(I MEAN I DONT KNOW PL/SQL etc).

2)I am using oracle 10g,in it SQL is running on command line.Is there any way to run 10g in windows environment like oracle 9i.

[Updated on: Wed, 21 July 2010 11:02]

Report message to a moderator

Re: count of characters without space for a name [message #466889 is a reply to message #466885] Wed, 21 July 2010 11:14 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Count the no. of characters with out considering spaces for each name.

Use REPLACE function to eliminate the spaces.

Quote:
2)I am using oracle 10g,in it SQL is running on command line.Is there any way to run 10g in windows environment like oracle 9i.

What do you mean?

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.
And do not post in UPPER case.

Regards
Michel
Re: count of characters without space for a name [message #466894 is a reply to message #466889] Wed, 21 July 2010 11:31 Go to previous messageGo to next message
raja_beginner
Messages: 20
Registered: July 2010
Location: india
Junior Member
Thanks for the reply.
In oracle 9i(sql*plus,form bulder) are there.
But in oracle 10g exp edition which i installed. it has the options:-
1)start/stop database.
2)run sql command line.
3)backup/restore DB.
4)Get help.
that is it.
But I want to run it like oracle 9i( i mean sql* plus).It is not happening with my installed versions 10g, even in 11g SQL has the option to open in command line.

How can I do it? I cant install oracle9i because i dont have enough memory/space in my pc.

[Updated on: Wed, 21 July 2010 11:39]

Report message to a moderator

Re: count of characters without space for a name [message #466898 is a reply to message #466894] Wed, 21 July 2010 11:47 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Were you trying to install a 10g database or 10g client software?
What exact version of 10g are you running?
What exactly do you run to get that list of options?
Re: count of characters without space for a name [message #466899 is a reply to message #466894] Wed, 21 July 2010 11:52 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You didn't install the options for forms but you should still have sqlplus.exe and sqlplusw.exe.

Regards
Michel
Re: count of characters without space for a name [message #466903 is a reply to message #466898] Wed, 21 July 2010 11:55 Go to previous messageGo to next message
raja_beginner
Messages: 20
Registered: July 2010
Location: india
Junior Member
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
I downloaded both 10g,11g(release 1) from oracle,both express editions.Both running SQL in command line.
I am using windows XP sp2.
All I want is how to run SQL on this version(10g) as SQL*plus in oracle 9i.
Any help on what wrong I did?


Re: count of characters without space for a name [message #466905 is a reply to message #466899] Wed, 21 July 2010 11:57 Go to previous messageGo to next message
raja_beginner
Messages: 20
Registered: July 2010
Location: india
Junior Member
Michel Cadot wrote on Wed, 21 July 2010 22:22
You didn't install the options for forms but you should still have sqlplus.exe and sqlplusw.exe.

Regards
Michel


Thanks for the suggestion. I will recheck the installation.

And also one final question on this,can you tell me which version you are using and how does SQL running on your machine,Is it working on command prompt or on windows environment(I mean like oracle 9i)

[Updated on: Wed, 21 July 2010 12:01]

Report message to a moderator

Re: count of characters without space for a name [message #466911 is a reply to message #466905] Wed, 21 July 2010 12:09 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I used all versions from 8i to 11gR2 on the same laptop.
All work well.

Quote:
or on windows environment(I mean like oracle 9i)

I still don't understand what you mean by this.
With ALL versions I use SQL*Plus mostly Windows version one (sqlplusw.exe).


Regards
Michel
Re: count of characters without space for a name [message #466938 is a reply to message #466885] Wed, 21 July 2010 13:02 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
raja_beginner wrote on Wed, 21 July 2010 11:58
If the name used in the statement has got more than one space,it isn't working.


You already discovered function LENGTH. Now discover function REPLACE: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions134.htm#i78608

SY.
P.S. You will also need function NVL.
Re: count of characters without space for a name [message #467086 is a reply to message #466911] Thu, 22 July 2010 04:23 Go to previous messageGo to next message
raja_beginner
Messages: 20
Registered: July 2010
Location: india
Junior Member
Michel Cadot wrote on Wed, 21 July 2010 22:39
I used all versions from 8i to 11gR2 on the same laptop.
All work well.

Quote:
or on windows environment(I mean like oracle 9i)

I still don't understand what you mean by this.
With ALL versions I use SQL*Plus mostly Windows version one (sqlplusw.exe).


Regards
Michel


Thank you for the answer.
Exactly,I am operating SQL on command prompt(like DOS) & I donot want to run SQL on command prompt.How can I change that.
At my institute they are using oracle 9i & they also dont know about my doubt.
Re: count of characters without space for a name [message #467093 is a reply to message #467086] Thu, 22 July 2010 04:33 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
When you say SQL do you mean SQLplus?
Re: count of characters without space for a name [message #467096 is a reply to message #466938] Thu, 22 July 2010 04:39 Go to previous messageGo to next message
raja_beginner
Messages: 20
Registered: July 2010
Location: india
Junior Member

You already discovered function LENGTH. Now discover function REPLACE: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions134.htm#i78608

SY.
P.S. You will also need function NVL.


Thank you very much. I red about replace today. I also tried it.
is it correct? please tell me.

SELECT LENGTH(REPLACE('RA JA SEKH A R',' ','')) FROM DUAL;

Result:- 10
I got the result,but you also suggested the use of nvl,can you please clarify me when exactly we use nvl in this condition/ in this types of query?.

Thanks in advance

regards
sona

[Updated on: Thu, 22 July 2010 04:40]

Report message to a moderator

Re: count of characters without space for a name [message #467098 is a reply to message #467093] Thu, 22 July 2010 04:45 Go to previous messageGo to next message
raja_beginner
Messages: 20
Registered: July 2010
Location: india
Junior Member
cookiemonster wrote on Thu, 22 July 2010 15:03
When you say SQL do you mean SQLplus?



Alas.. thank you. that is what I wanted.
Yes,in my installed version of oracle(10g) there is no SQL plus.
That is what I need. How to get that,which version of oracle software I need to download.I dont want oracle 9i or 8i.
Please specify a valid version of oracle so that I can run SQL*plus.I dont want to run SQL on command prompt.

[Updated on: Thu, 22 July 2010 04:47]

Report message to a moderator

Re: count of characters without space for a name [message #467102 is a reply to message #467098] Thu, 22 July 2010 04:47 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
All oracle client installs come with sqlplus by default as far as I'm aware.
So once again, what exactly did you install?
Re: count of characters without space for a name [message #467106 is a reply to message #467102] Thu, 22 July 2010 04:51 Go to previous messageGo to next message
raja_beginner
Messages: 20
Registered: July 2010
Location: india
Junior Member
cookiemonster wrote on Thu, 22 July 2010 15:17
All oracle client installs come with sqlplus by default as far as I'm aware.
So once again, what exactly did you install?


I downloaded & installed the EXE file from oracle.It is named "OracleXEUniv".(universal)
The size of the installer is 206 MB.
And
oracle 11g release1 database's size is 1.5 GB.In that also I am not able to find
SQL plus.
Do I have to download oracle libraries.
Is this wrong? If so specify me the right one please. Or please make time give me the link of it from Oracle official site.
Thanks for your support.


Regards,
sona

[Updated on: Thu, 22 July 2010 05:04]

Report message to a moderator

Re: count of characters without space for a name [message #467112 is a reply to message #467106] Thu, 22 July 2010 05:02 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Is this wrong?

I don't know.
Did you install it?
Have you SQL*Plus?

Quote:
Or please make time give me the link of it from Oracle official site.

http://www.oracle.com/technology/software/products/database/xe/index.html

Regards
Michel
Re: count of characters without space for a name [message #467123 is a reply to message #467112] Thu, 22 July 2010 05:09 Go to previous messageGo to next message
raja_beginner
Messages: 20
Registered: July 2010
Location: india
Junior Member
I installed it. I dont have SQL*Plus.

Quote:
Oracle Database 10g Express Edition (Universal)
OracleXEUniv.exe (216,933,372 bytes)
Multi-byte Unicode database for all language deployment, with the Database Homepage user interface available in the following languages: Brazilian Portuguese, Chinese (Simplified and Traditional), English, French, German, Italian, Japanese, Korean and Spanish.

The link you given,I downloaded the same. Please see the code,I specified it above.

[Updated on: Thu, 22 July 2010 05:15] by Moderator

Report message to a moderator

Re: count of characters without space for a name [message #467134 is a reply to message #467123] Thu, 22 July 2010 05:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I installed it. I dont have SQL*Plus.

I do not trust you. SQL*Plus is the SQL basic tool of Oracle, you should have it as it is in all and every Oracle download.

Regards
Michel
Re: count of characters without space for a name [message #467138 is a reply to message #467096] Thu, 22 July 2010 05:24 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
raja_beginner wrote on Thu, 22 July 2010 05:39

I got the result,but you also suggested the use of nvl,can you please clarify me when exactly we use nvl in this condition/ in this types of query?.


Well, I should hvae said "you might need NVL". If source string can be spaces only REPLACE will result in NULL and LENGTH will be NULL too. If you result in this case to be 0, use NVL:

> NVL(LENGTH(REPLACE(source_string,' ')),0)

And if you are on 11g, you can use REGEXP_COUNT:

> REGEXP_COUNT(source_string,'[^ ]')

SY.
Re: count of characters without space for a name [message #467147 is a reply to message #467134] Thu, 22 July 2010 05:34 Go to previous messageGo to next message
raja_beginner
Messages: 20
Registered: July 2010
Location: india
Junior Member
Michel Cadot wrote on Thu, 22 July 2010 15:47
Quote:
I installed it. I dont have SQL*Plus.

I do not trust you. SQL*Plus is the SQL basic tool of Oracle, you should have it as it is in all and every Oracle download.

Regards
Michel


Iam sorry,but I dont have SQL*plus,instead I has the option
start>
in all programs>
Oracle database 10g express edition>
Run SQL command line>

But my problem is a problem.I thought you friends might solve.But you simply said " I donot trust you",it's just humiliating when u said that.I am not a cheap trick player,I know it is time waste for both of us.
I am a mechanical student,but learning the oracle concepts,I dont know the wrong steps in my installation. I even checked the installation guide,but I cant find my fault.

Anyway thanks for the answers till now. Please stop the topic here,once again I am not the guy what you are thinking.

Bye for now and all.
Re: count of characters without space for a name [message #467148 is a reply to message #467138] Thu, 22 July 2010 05:35 Go to previous messageGo to next message
raja_beginner
Messages: 20
Registered: July 2010
Location: india
Junior Member
syakobson wrote on Thu, 22 July 2010 15:54
raja_beginner wrote on Thu, 22 July 2010 05:39

I got the result,but you also suggested the use of nvl,can you please clarify me when exactly we use nvl in this condition/ in this types of query?.


Well, I should hvae said "you might need NVL". If source string can be spaces only REPLACE will result in NULL and LENGTH will be NULL too. If you result in this case to be 0, use NVL:

> NVL(LENGTH(REPLACE(source_string,' ')),0)

And if you are on 11g, you can use REGEXP_COUNT:

> REGEXP_COUNT(source_string,'[^ ]')

SY.


Oh. thank you, this helps me in future. Thanks for the reply
Re: count of characters without space for a name [message #467149 is a reply to message #467148] Thu, 22 July 2010 05:55 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Have you actually searched your hard drive for a file called sqlplusw.exe?
It should be in a directory like:
C:\oracle\product\10.2.0\client_1\BIN
Re: count of characters without space for a name [message #468880 is a reply to message #467149] Mon, 02 August 2010 06:58 Go to previous message
martingh
Messages: 6
Registered: August 2010
Junior Member
let me know Were what exact version of 10g are you running and have you ever tried to install a 10g database or 10g client software?

Previous Topic: setting up of database schemas
Next Topic: C# .NET with oracle packages
Goto Forum:
  


Current Time: Thu Mar 28 16:11:13 CDT 2024