Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Roy Slough
  Where is Roy Slough?
 Potters Bar
 United Kingdom
 Roy Slough
 Tags
Subject: Name string not working in SQL
Thread ID: 345266 Message ID: 345266 # Views: 73 # Ratings: 0
Version: Visual FoxPro 8 Category: Databases, Tables and SQL Server
Date: Monday, May 21, 2012 9:30:37 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?

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