Outstanding! I wouldn't have figured that out in a week! Thanks! (works in SQL 2000 also)
Suppose I should titled this "How to find first letter of last word in string" - but this does the trick. For posterity....Here's the query that selects all records where last name starts with E select * from playerinfo where (select substring( reverse(playername) , (charindex(char(32), reverse(playername))-1) , 1 ) ) = 'E' D ************************ Diana Nichols Webmistress http://www.lavenderthreads.com 770.434.7374 -----Original Message----- From: Sicular, Alexander [mailto:[EMAIL PROTECTED]] Sent: Thursday, February 21, 2002 5:28 PM To: CF-Talk Subject: RE: How to find first letter of last name... Oops didn't see the 'sql' flag.... select substring( reverse(playername) , (charindex(char(32), reverse(playername))-1) , 1 ) as lastletter from playername Tested in mssql7 Good luck, alex |-----Original Message----- |From: Sicular, Alexander [mailto:[EMAIL PROTECTED]] |Sent: Thursday, February 21, 2002 4:55 PM |To: CF-Talk |Subject: RE: How to find first letter of last name... | | |<cfset playername = "firstname middlename lastname"> | |<cfoutput> | |#ListLen(playername , "#chr(32)#" )# |<br> |#listgetat(playername, "#ListLen(playername , "#chr(32)#" )#" |, "#chr(32)#" )# <br> #Left("#listgetat(playername, |"#ListLen(playername , "#chr(32)#" )#" , "#chr(32)#" )#" , 1)# | |</cfoutput> | |Good luck, | |Alexander Sicular |Chief Technology Architect |Neurological Institute of New York |Columbia University |as867 [at] columbia {dot} edu | | ||-----Original Message----- ||From: Diana Nichols [mailto:[EMAIL PROTECTED]] ||Sent: Thursday, February 21, 2002 3:35 PM ||To: CF-Talk ||Subject: How to find first letter of last name... || || ||I know there must be a simple way to do this....I have a ||field (playername) which contains "firstname middlename ||lastname". I want to select by the first letter of the ||lastname. I can do it with CF, or even JS, but can't figure ||out how to write the SQL.....ideas? || ||TIA! ||D || ||************************ ||Diana Nichols ||Webmistress ||http://www.lavenderthreads.com ||770.434.7374 || | ______________________________________________________________________ Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation � $99/Month � Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusiona FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
