Home » SQL & PL/SQL » SQL & PL/SQL » display info before a space
display info before a space [message #20116] Tue, 30 April 2002 05:05 Go to next message
Michael
Messages: 61
Registered: October 1999
Member
Assume a DB:
Name
Smith J B
Jones A X
I want to display surnames only
I have tried:
Select substring(name from 1 for position(' ' in name))
from name
But it just brings up 'invalid number'

I've tried other methods but it seems that the problem is - the 'position' function relates to the starting position of a substring and I want to specify the end.

I've even tried taking the 'J B' substring away from Smith J B - that doesn't work either.

Any ideas?

Report message to a moderator

Re: display info before a space [message #20117 is a reply to message #20116] Tue, 30 April 2002 05:16 Go to previous messageGo to next message
Mosquito
Messages: 6
Registered: April 2002
Junior Member
This doesn't exactly fit your problem description, but if you have to seperate names and surnames alot, maybe the most efficient thing would be to have them apart. One collumn for names and another for surnames.

Report message to a moderator

Re: display info before a space [message #20119 is a reply to message #20116] Tue, 30 April 2002 05:55 Go to previous message
sridhar
Messages: 119
Registered: December 2001
Senior Member
SUBSTR('first last', 1, instr('first last', ' ') - 1)
-- ' ' is single quote + space + single quote
Should give you 'first' like you wanted.
Thx,
Sri

Report message to a moderator

Previous Topic: MS-Access execute PL/SQL?
Next Topic: Re: Difference between oracle and SQL Server
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Apr 26 13:02:40 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.