 count of characters without space for a name [message #466885] |
Wed, 21 July 2010 10:58  |
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 #466894 is a reply to message #466889] |
Wed, 21 July 2010 11:31  |
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 #466903 is a reply to message #466898] |
Wed, 21 July 2010 11:55 |
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 |
raja_beginner
Messages: 20
Registered: July 2010
Location: india
|
Junior Member |
|
|
Michel Cadot wrote on Wed, 21 July 2010 22:22You 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 #467086 is a reply to message #466911] |
Thu, 22 July 2010 04:23 |
raja_beginner
Messages: 20
Registered: July 2010
Location: india
|
Junior Member |
|
|
Michel Cadot wrote on Wed, 21 July 2010 22:39I 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 #467098 is a reply to message #467093] |
Thu, 22 July 2010 04:45 |
raja_beginner
Messages: 20
Registered: July 2010
Location: india
|
Junior Member |
|
|
cookiemonster wrote on Thu, 22 July 2010 15:03When 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 #467106 is a reply to message #467102] |
Thu, 22 July 2010 04:51 |
raja_beginner
Messages: 20
Registered: July 2010
Location: india
|
Junior Member |
|
|
cookiemonster wrote on Thu, 22 July 2010 15:17All 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 #467123 is a reply to message #467112] |
Thu, 22 July 2010 05:09 |
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 #467138 is a reply to message #467096] |
Thu, 22 July 2010 05:24 |
Solomon Yakobson
Messages: 3303
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 |
raja_beginner
Messages: 20
Registered: July 2010
Location: india
|
Junior Member |
|
|
Michel Cadot wrote on Thu, 22 July 2010 15:47Quote: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 |
raja_beginner
Messages: 20
Registered: July 2010
Location: india
|
Junior Member |
|
|
syakobson wrote on Thu, 22 July 2010 15:54raja_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
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
