Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Mike Yearwood
  Where is Mike Yearwood?
 Toronto
 Canada
 Mike Yearwood
 To: Roy Slough
  Where is Roy Slough?
 Potters Bar
 United Kingdom
 Roy Slough
 Tags
Subject: RE: Name string not working in SQL
Thread ID: 345266 Message ID: 345324 # Views: 57 # Ratings: 1
Version: Visual FoxPro 8 Category: Databases, Tables and SQL Server
Date: Tuesday, May 22, 2012 3:49:58 PM         
   


> I HAVE FOUND THE SOLUTION TO THE PROBLEM BELOW
> (I do not know how to delete threads or even if I should - so perhaps the following will be of use to someone)
>
> The code needs to be...
>
>
> SELECT IIF(lcompany,Cname,PADR(substr(cName,RAT(" ",RTRIM(cName))+1),50))  as SortName FROM customers
> 

>
> Thanks for looking
>
> =============================================================================================
>
> Hi,
>
> I would like to sort my customers by the last word in the customer name (cName)
> the following code works in forms and the command window
>
>
> substr(cName,RAT(" ",RTRIM(cName))+1)
> 

>
> However IF I use the code in a query... e.g.
>
>
> SELECT substr(cName,RAT(" ",RTRIM(cName))+1) as sortname FROM customers
> 

> or even
>
> SELECT substr(cName,RAT(" ",RTRIM(cName))+1,30) as sortname FROM customers
> 

>
> I only get the first Character of the last word.
>
> I can always evaluate what the last word is and store it in the database, but this would be duplicating Data, which is a "bad" thing in database design.
>
> N.B I have a logical field indicating if this is a company name or not so my eventual select would be something like..
>
> SELECT IIF(lcompany,Cname,substr(cName,RAT(" ",RTRIM(cName))+1)) as SortName FROM customers
> 

>
>
> Any suggestions as to why this does not work?


I would not store first, middle, last in a single field. Use Anders' idea to move lastname to a new column. You can always combine strings to display.

Mike Yearwood
Microsoft MVP Visual FoxPro 2008, 2009
We have enough youth. We need a fountain of smart!
There may be many ways to skin a cat, but there are very few right ways to do it.

ENTIRE THREAD

Name string not working in SQL Posted by Roy Slough @ 5/21/2012 9:30:37 PM
RE: Name string not working in SQL Posted by Anders Altberg @ 5/22/2012 2:29:49 AM
RE: Name string not working in SQL Posted by Roy Slough @ 5/22/2012 9:26:01 AM
RE: Name string not working in SQL Posted by Anders Altberg @ 5/22/2012 11:43:01 AM
RE: Name string not working in SQL Posted by Mike Yearwood @ 5/22/2012 3:49:58 PM
RE: Name string not working in SQL Posted by Roy Slough @ 5/22/2012 4:02:45 PM
RE: Name string not working in SQL Posted by Mike Yearwood @ 5/22/2012 8:21:17 PM
RE: Name string not working in SQL Posted by Roy Slough @ 5/24/2012 8:48:35 AM
RE: Name string not working in SQL Posted by Mike Yearwood @ 5/24/2012 4:59:18 PM
RE: Name string not working in SQL Posted by tushar @ 5/24/2012 8:08:37 PM
RE: Name string not working in SQL Posted by Samir Ibrahim @ 5/30/2012 9:05:13 PM