Home » SQL & PL/SQL » SQL & PL/SQL » How to get first Word from string (Oracle 10g,xp)
| How to get first Word from string [message #594043] |
Thu, 22 August 2013 22:59  |
 |
hsinam
Messages: 29
Registered: February 2012
|
Junior Member |
|
|
Hi All,
could you any one please let me know how to get first word from string.
example i have string like:-
Jack Bore American
Mark D'suz Australian
Raj
Deniel indian
Some
i am expecting first word as out put like :-
jack
mark
Raj
Deniel
Some
in the same maner if i want should get two words also.
you pleaes genius guys help me.
Thanks,
|
|
|
|
|
|
|
|
|
|
| Re: How to get first Word from string [message #594051 is a reply to message #594046] |
Fri, 23 August 2013 01:13  |
|
hsinam
Messages: 29
Registered: February 2012
|
Junior Member |
|
|
Hi Swan,
i attached the sheet in that we have full_name colum value need to match with other table name column with starting two words and some of full_name column having only single word in the string that are not coming we need those also.
and some of case the string is containing total two words in the string also not coming..
i am trying with this query :-
select full_name,substr(full_name,1,instr(full_name,' ',1,2)) from test;
it's giving two words for the string who is having more than two words in string, not giving the string who is having only two words in the string.
or for single also not giving.
output:-
Input || output
Czech, Slovakia, Polish Vehicle Industry || Czech, Slovakia,
Dassault Aviation || coming null
DRL || coming null
where is null there i need value.
Please guys help me out from this i request you all.
Thanks,
|
|
|
|
| Re: How to get first Word from string [message #594054 is a reply to message #594043] |
Fri, 23 August 2013 01:39 |
 |
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
From your previous topics:
BlackSwan wrote on Tue, 20 August 2013 06:41...
Please read and follow the forum guidelines, to enable us to help you:
http://www.orafaq.com/forum/t/88153/0/
Michel Cadot wrote on Tue, 20 August 2013 07:28From your previous topic:
Michel Cadot wrote on Mon, 19 August 2013 09:28Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
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" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.
With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
Regards
Michel
BlackSwan wrote on Thu, 22 August 2013 06:15...
Please read and follow the forum guidelines, to enable us to help you:
http://www.orafaq.com/forum/t/88153/0/
...
hsinam wrote on Thu, 22 August 2013 07:03ohh Blackswan i'll do it but as of now i have also this much of information.please help me.
Michel Cadot wrote on Thu, 22 August 2013 08:06No, FIRST you follow the rules THEN we help, rules we has already pointed you to in your previous topics:
BlackSwan wrote on Tue, 20 August 2013 06:41realize that we do not have your table, so we can not test, run, improve or debug posted code.
Please read and follow the forum guidelines, to enable us to help you:
http://www.orafaq.com/forum/t/88153/0/
Michel Cadot wrote on Tue, 20 August 2013 07:28From your previous topic:
Michel Cadot wrote on Mon, 19 August 2013 09:28Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
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" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.
With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
Regards
Michel
You said you will follow the rules and you don't, you're a liar.
In addition, if you can't do it with the links that have been provided you are incompetent.
And FEEDBACK AND THANKS people that help or try to help you in any way.
Regards
Michel
[Updated on: Fri, 23 August 2013 01:42] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
| Re: How to get first Word from string [message #594060 is a reply to message #594058] |
Fri, 23 August 2013 02:28 |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
|
Why do you think that you are more special than other people on this forum nam? Why do you think that you don't need to follow the rules? You may have been lucky and been spoonfed the basis of an answer earlier, but trust me, people who are willing to do that will be few and far between. Your refusal to follow the rules and guidelines will very quickly mean that you will find it VERY difficult to get answers to your problems. Your choice, follow the rules, put in some effort of your own and get help. OR Consider yourself to be a special case, don't put any effort in, don't follow the rules and guidelines, get help on very few topics.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: How to get first Word from string [message #594075 is a reply to message #594063] |
Fri, 23 August 2013 03:05 |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
hsinam wrote on Fri, 23 August 2013 08:39Hi pab,
Oh My God why you saying this please do not htink negative about me .i'll follow all the rules.
please find my DB version and result snapshot in last reply:-
You keep saying this, and you keep NOT doing it.
|
|
|
|
|
|
|
|
| Re: How to get first Word from string [message #594100 is a reply to message #594095] |
Fri, 23 August 2013 08:25 |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
hsinam wrote on Fri, 23 August 2013 13:48Thanks a lot for this but how we can get two words. if i want to search for first and second word with in single query then how?
please if possible let me know.
As my string contain more then three words or some where two words how should i get this all words in single query.
like
output:-
Input || output
Czech, Slovakia, Polish Vehicle Industry || Czech, Slovakia,
Dassault Aviation || coming null
DRL || coming null
so here i have null for second and third row but i need value as they have in column.
please vboppa help me out as i am in need to complete this ASAP.
Quote:
Why do you think that you are more special than other people on this forum nam? Why do you think that you don't need to follow the rules? You may have been lucky and been spoonfed the basis of an answer earlier, but trust me, people who are willing to do that will be few and far between. Your refusal to follow the rules and guidelines will very quickly mean that you will find it VERY difficult to get answers to your problems. Your choice, follow the rules, put in some effort of your own and get help. OR Consider yourself to be a special case, don't put any effort in, don't follow the rules and guidelines, get help on very few topics.
|
|
|
|
| Re: How to get first Word from string [message #594109 is a reply to message #594095] |
Fri, 23 August 2013 10:47 |
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
hsinam wrote on Fri, 23 August 2013 14:48please vboppa help me out as i am in need to complete this ASAP.
It will never be complete if you change the requirements after each answer.
So please post a test case and the complete and final requirements / specifications ASAP to complete it ASAP.
|
|
|
|
| Re: How to get first Word from string [message #594113 is a reply to message #594067] |
Fri, 23 August 2013 11:05 |
|
|
Man,
i am using Toad and there is no option as copy or tag.not using Sql*plus
I dont know what is stopping you from following the rules , it will hardly takes few minutes for reading.You are asking for help then be polite and patient with seniors , people like blackswann,Michael and all others who replied you are of great help and they have tons of knowledge , please help yourself first to get the help properly.Why dont you follow ramya's advice , if its for lower case then there is one function UPPER in oracle use that and try yourself.You need to put in some effort from your side as well.I am also using toad and believe there are very good options you can explore.You can take the output of result in csv format and then you may paste it, there is a formatting options for formatting the queries and procedure as well.You can also prepare the insert scripts of table data.Please follow the rules as black swann suggested.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: How to get first Word from string [message #596393 is a reply to message #596389] |
Sun, 22 September 2013 08:09 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
dark_prince wrote on Sun, 22 September 2013 18:19Hey you can try this
SELECT SUBSTR('Jack Bore American', 1, (INSTR('Jack Bore American', ' ', 1))) "First Name"
FROM dual;
The output of your query will include a blank space in the end. You have to mention -1 in the INSTR part to get the correct result. Check Barbara's post.
SQL> SELECT SUBSTR('Jack Bore American',
2 1,
3 (INSTR('Jack Bore American', ' ', 1)-1)) "First Name"
4 FROM DUAL;
Regards,
Lalit
[Updated on: Sun, 22 September 2013 08:11] Report message to a moderator |
|
|
|
 Re: How to get first Word from string [message #596395 is a reply to message #596393] |
Sun, 22 September 2013 08:38 |
|
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
And it does not work for KIRAN example given above:
SQL> SELECT SUBSTR('KIRAN', 1, (INSTR('KIRAN', ' ', 1)-1)) "First Name"
2 from dual;
F
-
1 row selected.
So for the first question:
SQL> with data as (
2 select 'Jack Bore American' val from dual union all
3 select 'Mark D''suz Australian' val from dual union all
4 select 'Raj' val from dual union all
5 select 'Deniel indian' val from dual union all
6 select 'Some' val from dual
7 )
8 select val,
9 substr(val, 1, instr(val||' ', ' ')-1) res
10 from data
11 /
VAL RES
--------------------- ---------------------
Jack Bore American Jack
Mark D'suz Australian Mark
Raj Raj
Deniel indian Deniel
Some Some
for the second one:
SQL> with data as (
2 select 'Jack Bore American' val from dual union all
3 select 'Mark D''suz Australian' val from dual union all
4 select 'Raj' val from dual union all
5 select 'Deniel indian' val from dual union all
6 select 'Some' val from dual
7 )
8 select val,
9 substr(val, 1, instr(val||' ', ' ', 1, 2)-1) res
10 from data
11 /
VAL RES
--------------------- ---------------------
Jack Bore American Jack Bore
Mark D'suz Australian Mark D'suz
Raj Raj
Deniel indian Deniel indian
Some Some
[Updated on: Sun, 22 September 2013 08:40] Report message to a moderator |
|
|
|
|
|
| Re: How to get first Word from string [message #596402 is a reply to message #596395] |
Sun, 22 September 2013 12:14 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Just to summarize it into a single solution:
with data as (
select 'Jack Bore American' val from dual union all
select 'Mark D''suz Australian' val from dual union all
select 'Raj' val from dual union all
select 'Deniel indian' val from dual union all
select 'Some' val from dual
)
select val,
substr(val,1,instr(val || lpad(' ',:n),' ',1,:n)) res -- where :n is desired number
from data
/
For example:
SQL> variable n
variable n
datatype NUMBER
SQL> exec :n := 1;
PL/SQL procedure successfully completed.
SQL> with data as (
2 select 'Jack Bore American' val from dual union all
3 select 'Mark D''suz Australian' val from dual union all
4 select 'Raj' val from dual union all
5 select 'Deniel indian' val from dual union all
6 select 'Some' val from dual
7 )
8 select val,
9 substr(val,1,instr(val || lpad(' ',:n),' ',1,:n)) res
10 from data
11 /
VAL RES
--------------------- ---------------------
Jack Bore American Jack
Mark D'suz Australian Mark
Raj Raj
Deniel indian Deniel
Some Some
SQL> exec :n := 2;
PL/SQL procedure successfully completed.
SQL> with data as (
2 select 'Jack Bore American' val from dual union all
3 select 'Mark D''suz Australian' val from dual union all
4 select 'Raj' val from dual union all
5 select 'Deniel indian' val from dual union all
6 select 'Some' val from dual
7 )
8 select val,
9 substr(val,1,instr(val || lpad(' ',:n),' ',1,:n)) res
10 from data
11 /
VAL RES
--------------------- ---------------------
Jack Bore American Jack Bore
Mark D'suz Australian Mark D'suz
Raj Raj
Deniel indian Deniel indian
Some Some
SQL> exec :n := 3;
PL/SQL procedure successfully completed.
SQL> with data as (
2 select 'Jack Bore American' val from dual union all
3 select 'Mark D''suz Australian' val from dual union all
4 select 'Raj' val from dual union all
5 select 'Deniel indian' val from dual union all
6 select 'Some' val from dual
7 )
8 select val,
9 substr(val,1,instr(val || lpad(' ',:n),' ',1,:n)) res
10 from data
11 /
VAL RES
--------------------- ---------------------
Jack Bore American Jack Bore American
Mark D'suz Australian Mark D'suz Australian
Raj Raj
Deniel indian Deniel indian
Some Some
SQL>
SY.
|
|
|
|
|
|
|
|
| Re: How to get first Word from string [message #596793 is a reply to message #596792] |
Thu, 26 September 2013 06:21 |
 |
dark_prince
Messages: 121
Registered: June 2013
Location: India
|
Senior Member |
|
|
Thanks little foot, I tried but every time it giving me different output. you can check, I've created procedure which gives output as full name, first_name, mid_name, last_name and its corresponding length after giving the input as string.
CREATE OR REPLACE PROCEDURE get_string (string IN VARCHAR2)
IS
full_name VARCHAR2(50);
first_name VARCHAR2(50);
mid_name VARCHAR2(50);
last_name VARCHAR2(50);
first_value NUMBER;
second_value NUMBER;
full_name_value NUMBER;
first_name_value NUMBER;
mid_name_value NUMBER;
last_name_value NUMBER;
BEGIN
full_name := string;
first_value := INSTR(full_name, ' ', 1, 1);
second_value := INSTR(full_name, ' ', 1, 2);
SELECT SUBSTR(full_name, 1, first_value - 1),
SUBSTR(full_name, first_value + 1, INSTR(full_name, ' ', 1, 1) -2),
SUBSTR(full_name, second_value + 1, INSTR(full_name, ' ', -1, 1) -2)
INTO first_name, mid_name, last_name
FROM dual;
full_name_value := LENGTH(full_name);
first_name_value := LENGTH(first_name);
mid_name_value := LENGTH(mid_name);
last_name_value := LENGTH(last_name);
dbms_output.put_line('Full Name: '||full_name||' and its value is '||full_name_value);
dbms_output.put_line('First Name: '||first_name||' and its value is '||first_name_value);
dbms_output.put_line('Middle Name: '||mid_name||' and its value is '||mid_name_value);
dbms_output.put_line('Last Name: '||last_name||' and its value is '||last_name_value);
dbms_output.put_line('First Value: '||first_value);
dbms_output.put_line('Second Value: '||second_value);
END;
after calling the procedure
CALL get_string('JACK JOSEPH SPARROW');
It gives following o/p
Full Name: JACK JOSEPH SPARROW and its value is 19
First Name: JACK and its value is 4
Middle Name: JOS and its value is 3
Last Name: SPARROW and its value is 7
First Value: 5
Second Value: 12
As you can see, the mid_name is not coming properly...please help me with this
|
|
|
|
| Re: How to get first Word from string [message #596798 is a reply to message #596793] |
Thu, 26 September 2013 06:34 |
 |
Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
You chose to find the middle name asSUBSTR(full_name, first_value + 1, INSTR(full_name, ' ', 1, 1) -2),
SUBSTR does this:
- full_name -> JACK JOSEPH SPARROW
- first_value + 1 -> it is position of the first space character + 1, i.e. "J" in "JOSEPH"
- the third parameter is length - you said that it is equal to the position of the first space minus 2 positions, i.e. 5 - 2 = 3
Result? JACK JOSEPH SPARROW.
You need to fix the third SUBSTR parameter, i.e. length of the substring.
|
|
|
|
| Re: How to get first Word from string [message #596826 is a reply to message #596798] |
Thu, 26 September 2013 09:01 |
|
dark_prince
Messages: 121
Registered: June 2013
Location: India
|
Senior Member |
|
|
Hey thanks for your advise little foot..I make some modification in the procedure and now it giving correct output. Here is the procedure
CREATE OR REPLACE PROCEDURE get_string (string IN VARCHAR2)
IS
full_name VARCHAR2(50);
first_name VARCHAR2(50);
mid_name VARCHAR2(50);
last_name VARCHAR2(50);
first_value NUMBER;
second_value NUMBER;
third_value NUMBER;
full_name_value NUMBER;
first_name_value NUMBER;
mid_name_value NUMBER;
last_name_value NUMBER;
BEGIN
full_name := string;
first_value := INSTR(full_name, ' ', 1, 1);
third_value := INSTR(full_name, ' ', 1, 2);
second_value := third_value - first_value;
SELECT SUBSTR(full_name, 1, first_value - 1),
SUBSTR(full_name, first_value + 1, second_value - 1),--INSTR(full_name, ' ', 1, 1) -2),
SUBSTR(full_name, third_value + 1, INSTR(full_name, ' ', -1, 1) -2)
INTO first_name, mid_name, last_name
FROM dual;
full_name_value := LENGTH(full_name);
first_name_value := LENGTH(first_name);
mid_name_value := LENGTH(mid_name);
last_name_value := LENGTH(last_name);
dbms_output.put_line('Full Name : '||full_name||' and its Value is '||full_name_value);
dbms_output.put_line('First Name : '||first_name||' and its Value is '||first_name_value);
dbms_output.put_line('Middle Name : '||mid_name||' and its Value is '||mid_name_value);
dbms_output.put_line('Last Name : '||last_name||' and its Value is '||last_name_value);
dbms_output.put_line('First Value : '||first_value);
dbms_output.put_line('Second Value : '||second_value);
dbms_output.put_line('Third Value : '||third_value);
END;
Please verify it... 
|
|
|
|
Goto Forum:
Current Time: Thu Apr 25 22:26:29 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
