Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2011
    Posts
    10

    Unanswered: SUBSTR and INSTR problem

    I am in a DB class and am having trouble with one part of a question. I need to take the vendor_name and drop the first name of the vendor_name which I ran like this:

    SUBSTR(vendor_name, (INSTR(vendor_name, ' ') +1)) AS second_name

    Which returns values such as Postal Service (for US Postal Service) and Jobtrak (for Jobtrak)
    What I am trying to do now is when the vendor_name only has one name in it (such as Jobtrak), the second_name column needs to be blank. I know I need to find the length of the substring which I have found the difference but do not know how to proceed from there:

    LENGTH(vendor_name) - LENGTH(SUBSTR(vendor_name, - (INSTR(vendor_name, ' ')) +1)) AS second_name
    Any help is much appreciated

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    CASE (or DECODE, if you prefer) could help:
    Code:
    SQL> with test as
      2    (select 'US Postal office' vendor from dual union
      3     select 'Jobtrak' from dual
      4    )
      5  select
      6    vendor,
      7    case when instr(vendor, ' ') = 0 then
      8              null
      9         else substr(vendor, instr(vendor, ' ') + 1)
     10    end second_name
     11  from test;
    
    VENDOR           SECOND_NAME
    ---------------- --------------------
    Jobtrak
    US Postal office Postal office
    
    SQL>

  3. #3
    Join Date
    Jun 2011
    Posts
    10
    Thank you Littlefoot. I did have modify one statement but was easily fixed:

    WHEN instr(vendor_name, ' ') = 0 THEN ' '
    Quote Originally Posted by Littlefoot View Post
    CASE (or DECODE, if you prefer) could help:
    Code:
    SQL> with test as
      2    (select 'US Postal office' vendor from dual union
      3     select 'Jobtrak' from dual
      4    )
      5  select
      6    vendor,
      7    case when instr(vendor, ' ') = 0 then
      8              null  
      9         else substr(vendor, instr(vendor, ' ') + 1)
     10    end second_name
     11  from test;
    
    VENDOR           SECOND_NAME
    ---------------- --------------------
    Jobtrak
    US Postal office Postal office
    
    SQL>

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Why "blank" ('<blank>', ' ') instead of NULL?

    Blank is a character (a space). NULL means "it does not exist"; in your example, "Jobtrak" really doesn't have the second part of the name - it does not exist, so it should be NULL. It is not that the second part is a blank, nope - it is really NULL.

  5. #5
    Join Date
    Jun 2011
    Posts
    10
    Quote Originally Posted by Littlefoot View Post
    Why "blank" ('<blank>', ' ') instead of NULL?

    Blank is a character (a space). NULL means "it does not exist"; in your example, "Jobtrak" really doesn't have the second part of the name - it does not exist, so it should be NULL. It is not that the second part is a blank, nope - it is really NULL.
    When I used NULL, the return query actually had the word "NULL" in the fields that should have been blank. When I switched the statement to ' ', that is when the return fields that should have been blank were blank. This is just a question in a book, so many of the columns that had me create were useless and really had no value added to the table.

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Well, that might be your tool's setting. NULL is a NULL; you should have set it differently. Here's a SQL*Plus example:
    Code:
    SQL> create table test (id number, name varchar2(20));
    
    Table created.
    
    SQL> insert into test values (1, 'Little');
    
    1 row created.
    
    SQL> insert into test values (2, null);
    
    1 row created.
    Code:
    SQL> select * from test;
    
            ID NAME
    ---------- --------------------
             1 Little
             2
    
    SQL> set null '???'
    SQL> select * from test;
    
            ID NAME
    ---------- --------------------
             1 Little
             2 ???
    
    SQL> set null 'NULL'
    SQL> select * from test;
    
            ID NAME
    ---------- --------------------
             1 Little
             2 NULL
    
    SQL>

  7. #7
    Join Date
    Jun 2011
    Posts
    10
    Quote Originally Posted by Littlefoot View Post
    Well, that might be your tool's setting. NULL is a NULL; you should have set it differently.
    Yes, my settings could very well be wrong. As you can tell, I am very new to sql. Thanks for the replys. I learned some new tricks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •