[dba-SQLServer] Find the second occurrence of a character in astring

Stuart,

SELECT     LTRIM(RIGHT(pub_name, CHARINDEX(' ', REVERSE(pub_name)))) AS
Expr1
FROM         publishers

The -1 throws an error if there are no spaces.
This works fine and is better then mine.

cheers

Michael M

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Stuart
McLachlan
Sent: Wednesday, 20 December 2006 2:06 PM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] Find the second occurrence of a character
in astring

How about (aircode):
RIGHT(FullName,CHARINDEX(' ',REVERSE(FullName))-1)


On 19 Dec 2006 at 18:27, artful at rogers.com wrote:

> Assume these two values in two rows in the table of interest:
> 
> Andrei Pascal
> Arthur B. Fuller
> 
> I need to parse out the surname. I can't just grab the characters 
> following the first space, unless there is only one space. But if 
> there are two spaces, then I need the trailing string following the
second space.
> 
> CHARINDEX( 'f', 'abcdefgh' ) returns 6  so that part is easy. How to 
> find the second occurrence, if any? In fact, assuming really bizarre 
> data, what I want is the string following the last space in the value.
> 
> Even that may not work, given Portuguese and Spanish and French 
> surnames, but for now I will live with that.
> 
> Suppose an entry such as:
> 
> Juan Carlos de la Vega
> 
> I need to grab the string following the last space, in T-SQL syntax.
> 
> Any ideas?
> TIA,
> Arthur
> 
> 
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
> 

--
Stuart


_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com