Results 1 to 8 of 8

Thread: Oracle SQL

  1. #1
    Funking Prink! Raz316's Avatar
    Join Date
    Jul 2003
    Location
    Deal, Kent, UK
    Posts
    2,978
    Thanks
    130
    Thanked
    62 times in 52 posts

    Oracle SQL

    Oracle version 8.1.7

    Ok I am very new to this and am currently following O'Reilly's guide to 'Mastering Oracle SQL' which is actually for oracle9... (not my fault, I didnt buy the thing!)

    anyway, here is where I am..
    Code:
    CHRIS:CHRIS>> select * from tblfirst;
    
    NAME                                  AGE
    ------------------------------ ----------
    Chris                                  26
    Simon                                  62
    Code:
    CHRIS:CHRIS>> select * from tblfirst where name like '_hris';
    
    no rows selected
    Is the use of _ (which can match any single character) not available in my version of oracle, or have I got something wrong here?

    Cheers

  2. #2
    Agent of the System ikonia's Avatar
    Join Date
    May 2004
    Location
    South West UK (Bath)
    Posts
    3,736
    Thanks
    39
    Thanked
    75 times in 56 posts
    I'm not sure _ is a PL/SQL charecter where you are putting the like statment in, I think _ problems. try using the like on say "hris" see if it picks up "Chris"
    It is Inevitable.....


  3. #3
    Funking Prink! Raz316's Avatar
    Join Date
    Jul 2003
    Location
    Deal, Kent, UK
    Posts
    2,978
    Thanks
    130
    Thanked
    62 times in 52 posts
    cheers ikonia

    like 'hris'; - no rows
    like 'Chri'; - no rows
    like '%is'; - no rows
    like 'Chr%' - returns a row.

  4. #4
    bored out of my tiny mind malfunction's Avatar
    Join Date
    Jul 2003
    Location
    Lurking
    Posts
    3,923
    Thanks
    191
    Thanked
    187 times in 163 posts
    • malfunction's system
      • Motherboard:
      • Gigabyte G1.Sniper (with daft heatsinks and annoying Killer NIC)
      • CPU:
      • Xeon X5670 (6 core LGA 1366) @ 4.4GHz
      • Memory:
      • 48GB DDR3 1600 (6 * 8GB)
      • Storage:
      • 1TB 840 Evo + 1TB 850 Evo
      • Graphics card(s):
      • 290X
      • PSU:
      • Antec True Power New 750W
      • Case:
      • Cooltek W2
      • Operating System:
      • Windows 10
      • Monitor(s):
      • Dell U2715H
    That's a weird one - I'm sure it SHOULD work as you originally have it (though I'm a bit rusty when it comes to Oracle)...So should '%is' and 'Chr%'... Unless the field is declared in a weird way or there's garbage in there...? Does '%' (and '_' for that matter) return all rows? *MAYBE* it doesn't like you using 'name' as a column name?

  5. #5
    LWA
    LWA is offline
    Senior Member
    Join Date
    Jul 2003
    Location
    London
    Posts
    2,171
    Thanks
    134
    Thanked
    57 times in 41 posts
    Raz316,
    I have just created your table and here's what I get:

    Code:
    Leon: select * from tblfirst;
    
    NAME                AGE
    ---------------------
    Chris                  26
    Simon                 62
    
    Leon: select * from tblfirst where name like '_hris';
    
    NAME                                    AGE
    ----------------------------------------
    Chris                                     26
    I am using SQL PLUS version 8.0.6.0.0 and it works fine.

    Are you sure you are storing the names in both upper and lower case and not just in upper case?

    Name is a reserved word so whether that does have an affect on your version I'm not sure. Try creating the table with first_name as the colomn name instead?

    Leon
    Last edited by LWA; 08-06-2004 at 01:20 PM.

  6. #6
    Funking Prink! Raz316's Avatar
    Join Date
    Jul 2003
    Location
    Deal, Kent, UK
    Posts
    2,978
    Thanks
    130
    Thanked
    62 times in 52 posts
    Code:
    CHRIS:CHRIS>> select * from tblfirst;
    
    NAME                                  AGE
    ------------------------------ ----------
    Chris                                  26
    Simon                                  62
    if I do.
    where age like '%6'; - returns one
    where age like '2_'; - returns one too

    wierd, it could be the field, it is currently set as char(30) because doing the commands on another table with a field called name but set as varchar2(30) works.

  7. #7
    LWA
    LWA is offline
    Senior Member
    Join Date
    Jul 2003
    Location
    London
    Posts
    2,171
    Thanks
    134
    Thanked
    57 times in 41 posts
    Quote Originally Posted by Raz316
    if I do.
    where age like '%6'; - returns one
    where age like '2_'; - returns one too
    ^^This is correct^^

    The first statement matches only one because it is looking for anything with a '6' on the end i.e. 26. So this could not match 62.

    The second one matches only one because it is looking for the number '2' then any character i.e. 26 . So this could not match 62.

  8. #8
    Funking Prink! Raz316's Avatar
    Join Date
    Jul 2003
    Location
    Deal, Kent, UK
    Posts
    2,978
    Thanks
    130
    Thanked
    62 times in 52 posts
    yeah I know, sorry, i was just proving that it actually works on the age field [number(2,0)] but not the original name field [char(30)]

    Sorted now, ill just use varchar2() instead.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Learning SQL
    By Lead_Head in forum Software
    Replies: 13
    Last Post: 26-05-2004, 06:54 PM
  2. more VBA and SQL 'problems'...
    By streetster in forum Software
    Replies: 7
    Last Post: 07-01-2004, 10:45 PM
  3. SQL help
    By Agent in forum Software
    Replies: 23
    Last Post: 11-11-2003, 09:22 AM

Posting Permissions

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