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

Good point.
I did call it as "aircode" and didn't think about exceptions to the 
examples.

On 20 Dec 2006 at 14:19, Michael Maddison wrote:

> 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
> 
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
> 

-- 
Stuart