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 Go to next message
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 #594044 is a reply to message #594043] Thu, 22 August 2013 23:07 Go to previous messageGo to next message
BlackSwan
Messages: 21949
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

RTFM/Google for INSTR & SUBSTR
Re: How to get first Word from string [message #594045 is a reply to message #594044] Thu, 22 August 2013 23:22 Go to previous messageGo to next message
hsinam
Messages: 29
Registered: February 2012
Junior Member
thanks Swan..i googled it but not getting the result.
Re: How to get first Word from string [message #594046 is a reply to message #594045] Thu, 22 August 2013 23:24 Go to previous messageGo to next message
BlackSwan
Messages: 21949
Registered: January 2009
Senior Member
I am sorry to see that GOOGLE is broken for you.
Please be patient while repairs are completed.
Additional information will be posted when it becomes available.

http://docs.oracle.com/cd/E16655_01/server.121/e17209/functions087.htm#SQLRF00651
Re: How to get first Word from string [message #594051 is a reply to message #594046] Fri, 23 August 2013 01:13 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 57612
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:28
From your previous topic:

Michel Cadot wrote on Mon, 19 August 2013 09:28
Please 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:03
ohh 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:06
No, 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:41
realize 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:28
From your previous topic:

Michel Cadot wrote on Mon, 19 August 2013 09:28
Please 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 #594055 is a reply to message #594051] Fri, 23 August 2013 01:52 Go to previous messageGo to next message
ramya_162
Messages: 80
Registered: August 2013
Location: Banglore
Member
Hi Friend.

To get first word from string use this SQL.

SELECT SUBSTR('KIRAN KUMAR',1,INSTR('KIRAN KUMAR',' ')-1) "first word" FROM DUAL;

Thanks.
Re: How to get first Word from string [message #594056 is a reply to message #594055] Fri, 23 August 2013 02:05 Go to previous messageGo to next message
hsinam
Messages: 29
Registered: February 2012
Junior Member
thanks Ramya,
and what to do if letter is small?

SELECT SUBSTR('kiran',1,INSTR('Kiran',' ')-1) "first word" FROM DUAL;

my some of row having single word with lower then how do we get .

please help me out.

Thanks,


Re: How to get first Word from string [message #594057 is a reply to message #594055] Fri, 23 August 2013 02:09 Go to previous messageGo to next message
hsinam
Messages: 29
Registered: February 2012
Junior Member
and one more thing if some of row contain only single word then

like

SELECT SUBSTR('KIRAN',1,INSTR('KIRAN',' ')-1) "first word" FROM DUAL;

it's not giving me the result as first word.
Re: How to get first Word from string [message #594058 is a reply to message #594054] Fri, 23 August 2013 02:21 Go to previous messageGo to next message
hsinam
Messages: 29
Registered: February 2012
Junior Member
Hi Michel/BlackSwan,
nothing is like that i did not get time thats why please consider this attachment for the time being.

there you can see my missing data in result column.
please help me out ASAP.

Thanks,
  • Attachment: SubStr.jpg
    (Size: 752.43KB, Downloaded 54 times)
Re: How to get first Word from string [message #594060 is a reply to message #594058] Fri, 23 August 2013 02:28 Go to previous messageGo to next message
pablolee
Messages: 2590
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 #594063 is a reply to message #594060] Fri, 23 August 2013 02:39 Go to previous messageGo to next message
hsinam
Messages: 29
Registered: February 2012
Junior Member
Hi 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:-

  • Attachment: version.jpg
    (Size: 240.52KB, Downloaded 45 times)
Re: How to get first Word from string [message #594065 is a reply to message #594063] Fri, 23 August 2013 02:43 Go to previous messageGo to next message
cookiemonster
Messages: 10573
Registered: September 2008
Location: Rainy Manchester
Senior Member
And you're still not following the rules - post query executions and results in code tags, not as images.
Re: How to get first Word from string [message #594067 is a reply to message #594065] Fri, 23 August 2013 02:50 Go to previous messageGo to next message
hsinam
Messages: 29
Registered: February 2012
Junior Member
Man,
i am using Toad and there is no option as copy or tag.not using Sql*plus
Re: How to get first Word from string [message #594071 is a reply to message #594067] Fri, 23 August 2013 02:56 Go to previous messageGo to next message
Michel Cadot
Messages: 57612
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But in forum you MUST use SQL*Plus.
We don't care what you usually use.

Regards
Michel
Re: How to get first Word from string [message #594072 is a reply to message #594071] Fri, 23 August 2013 02:59 Go to previous messageGo to next message
Roachcoach
Messages: 1125
Registered: May 2010
Location: UK
Senior Member
Dont most tools these days have some manner of "sql window" that to most purposes works well enough for a forum copy/paste?
Re: How to get first Word from string [message #594075 is a reply to message #594063] Fri, 23 August 2013 03:05 Go to previous messageGo to next message
pablolee
Messages: 2590
Registered: May 2007
Location: Scotland
Senior Member
hsinam wrote on Fri, 23 August 2013 08:39
Hi 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 #594093 is a reply to message #594075] Fri, 23 August 2013 07:08 Go to previous messageGo to next message
vboppa
Messages: 22
Registered: November 2008
Junior Member
Hi,

You can use the below query to get the first word of the string.

SELECT SUBSTR (full_name,
1,
REPLACE (INSTR (full_name, ' ') - 1, -1,
LENGTH (full_name))
)
FROM TEST;

Bye.
Re: How to get first Word from string [message #594095 is a reply to message #594093] Fri, 23 August 2013 07:48 Go to previous messageGo to next message
hsinam
Messages: 29
Registered: February 2012
Junior Member
Thanks 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.
Re: How to get first Word from string [message #594100 is a reply to message #594095] Fri, 23 August 2013 08:25 Go to previous messageGo to next message
pablolee
Messages: 2590
Registered: May 2007
Location: Scotland
Senior Member
hsinam wrote on Fri, 23 August 2013 13:48
Thanks 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 Go to previous messageGo to next message
ThomasG
Messages: 3064
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
hsinam wrote on Fri, 23 August 2013 14:48
please 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 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

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 #594114 is a reply to message #594113] Fri, 23 August 2013 11:49 Go to previous messageGo to next message
hsinam
Messages: 29
Registered: February 2012
Junior Member
Hi Arif,
thanks for correction and to improve my way of speaking but i gave already my data sheet and version of task.

the result i was expecting i mentioned above but tech people are doing in different way some where matching with my requirement or some where not.

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.

above output i am expecting first two rows as up to two words along third row single word,please see my output column.
i appreciate every one work on this task including ramya,boppa and all. i tried with lower and upper case also.
i am sorry if any one hurts by my words.

Thanks,
Re: How to get first Word from string [message #594117 is a reply to message #594114] Fri, 23 August 2013 11:59 Go to previous messageGo to next message
BlackSwan
Messages: 21949
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

We can't write SQL when we don't have table name, column name or data.

To get left two words
1) start with string
2) find leftmost word
3) remove leftmost word from string
4) find leftmost word from shortened string
Re: How to get first Word from string [message #594118 is a reply to message #594114] Fri, 23 August 2013 12:01 Go to previous messageGo to next message
Michel Cadot
Messages: 57612
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Still out of the rules.
I wonder if you are not just a troll or are just completely dumb.
It is not possible for one that has more than 2 neurons to continue in the way you do.
Anyway, I think we should stop and lock this topic that goes nowhere.

Regards
Michel
Re: How to get first Word from string [message #596367 is a reply to message #594114] Sat, 21 September 2013 13:57 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7860
Registered: November 2002
Location: California, USA
Senior Member
select substr (full_name, 1, instr (full_name || '  ', ' ', 1, 2) - 1) 
from   test;

[Updated on: Sat, 21 September 2013 13:58]

Report message to a moderator

Re: How to get first Word from string [message #596372 is a reply to message #596367] Sat, 21 September 2013 22:38 Go to previous messageGo to next message
m.abdulhaq
Messages: 86
Registered: April 2013
Location: Ajman
Member
Excellent example mam.thanks a lot.
Re: How to get first Word from string [message #596389 is a reply to message #594043] Sun, 22 September 2013 07:49 Go to previous messageGo to next message
dark_prince
Messages: 98
Registered: June 2013
Location: India
Member
Hey you can try this
SELECT  SUBSTR('Jack Bore American', 1, (INSTR('Jack Bore American', ' ', 1))) "First Name"
FROM    dual;
Re: How to get first Word from string [message #596393 is a reply to message #596389] Sun, 22 September 2013 08:09 Go to previous messageGo to next message
Lalit Kumar B
Messages: 1087
Registered: May 2013
Location: Hi-tech city
Senior Member
dark_prince wrote on Sun, 22 September 2013 18:19
Hey 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

icon13.gif  Re: How to get first Word from string [message #596395 is a reply to message #596393] Sun, 22 September 2013 08:38 Go to previous messageGo to next message
Michel Cadot
Messages: 57612
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 #596397 is a reply to message #596393] Sun, 22 September 2013 08:48 Go to previous messageGo to next message
dark_prince
Messages: 98
Registered: June 2013
Location: India
Member
Yes I get it thanks... Sad
Re: How to get first Word from string [message #596402 is a reply to message #596395] Sun, 22 September 2013 12:14 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1794
Registered: January 2010
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 #596790 is a reply to message #596393] Thu, 26 September 2013 05:57 Go to previous messageGo to next message
dark_prince
Messages: 98
Registered: June 2013
Location: India
Member
Hey can you please tell of how to middle and last string from full name like 'jack joseph sparrow' 'joseph' and 'sparrow'..
Re: How to get first Word from string [message #596792 is a reply to message #596790] Thu, 26 September 2013 06:06 Go to previous messageGo to next message
Littlefoot
Messages: 18833
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If you carefully read this thread, you have learnt that SUBSTR and INSTR functions are rather helpful. Therefore, read how they are used and try it yourself. If you can't do it, come back here, show us what you did, how Oracle responded, and someone will assist.
Re: How to get first Word from string [message #596793 is a reply to message #596792] Thu, 26 September 2013 06:21 Go to previous messageGo to next message
dark_prince
Messages: 98
Registered: June 2013
Location: India
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 Go to previous messageGo to next message
Littlefoot
Messages: 18833
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You chose to find the middle name as
SUBSTR(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 Go to previous message
dark_prince
Messages: 98
Registered: June 2013
Location: India
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... Smile
Previous Topic: V$ tables interrogate
Next Topic: Same source code giving different results
Goto Forum:
  


Current Time: Sun Apr 20 02:00:16 CDT 2014

Total time taken to generate the page: 0.10185 seconds