display info prior to space - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

display info prior to space

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-05-02 : 10:39:37

michael writes "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?"

aclarke
Posting Yak Master

133 Posts

Posted - 2002-05-02 : 11:46:38


declare @fullName varchar(50)
set @fullName = 'Smith J B'
select left(@fullName, (len(@fullName) - 4))
select left(@fullName, charIndex(' ', @fullName))

The first one works when you always have two initials just like your examples.
The second one works as long as nobody has a last name like 'Van Deusenberg' or something.
You'll have to come up with your own logic based upon the data.

Of course, the best way is usually to store these things in different fields if you can manage it...

Edited by - aclarke on 05/02/2002 11:49:17

TonyH
Starting Member

29 Posts

Posted - 2002-05-02 : 14:54:24

Hi,

The following code may help.

declare @n varchar(30)
select @n = 'van morrison d'

select 'surname' =
case substring(@n+' ', charindex(' ',@n+' ')+2 , 1)
when ' '
then substring(@n+' ',1,charindex(' ',@n+' ')-1 )
else
substring(@n+' ',1 ,charindex(' ',@n+' '+' ')-1)+
substring(@n+' ',
charindex(' ',@n+' '),
charindex(' ',@n+' ' , charindex(' ',@n+' ')+1) - charindex(' ',@n+' '))
end

TonyH
www.SQLCoder.com - Free Code generation for SQL Server 7/2000

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources