Eric
You can use this UDF too
CREATE FUNCTION dbo.getLastName(@fullname varchar(200))
RETURNS varchar(50)
AS
begin
Set @fullname = substring(@fullname,Charindex(' ',@fullname) + 1
,len(@fullname))
Set @fullname = substring(@fullname,1 ,charindex( ' ', @fullname ))
return @fullname
END
On Thu, 24 Feb 2005 10:43:04 -0600, Eric Creese <[EMAIL PROTECTED]> wrote:
> I will give it a try. I wnet ahead and did it in two steps and got the data I
> want but to get it all in one would be what I would like.
>
> -----Original Message-----
> From: Adrian Lynch [mailto:[EMAIL PROTECTED]
> Sent: Thursday, February 24, 2005 10:44 AM
> To: CF-Talk
> Subject: RE: OT-SQL matching
>
> My bad, I misunderstood.
>
> Run this and wack in some more sample data to see if it works
>
> CREATE TABLE #temp ( Names VARCHAR(100) )
>
> INSERT INTO #temp ( Names ) VALUES ( 'LITTLE DONALD V' )
> INSERT INTO #temp ( Names ) VALUES ( 'LITTLE DONALD VICTOR' )
> INSERT INTO #temp ( Names ) VALUES ( 'LITTLE DONALD Victor JR' )
>
> SELECT
> '::' + SUBSTRING(Names, 1, CHARINDEX(' ', Names, 1) - 1) + '::'
> 'FirstName',
> '::' + SUBSTRING(Names, CHARINDEX(' ', Names, 1) + 1, CHARINDEX(' ',
> Names,
> CHARINDEX(' ', Names, 1)) - 1) + '::' 'LastName'
> FROM #temp
>
> DROP TABLE #temp
>
> I've put :: on either side to make sure no white space is being returned
> along with the name.
>
> Ade
>
> -----Original Message-----
> From: Eric Creese [mailto:[EMAIL PROTECTED]
> Sent: 24 February 2005 15:56
> To: CF-Talk
> Subject: RE: OT-SQL matching
>
> It is always
>
> LASTNAME FIRSTNAME then ......
> LITTLE DONALD Victor JR
>
> So I just want
> LASTNAME = LITTLE
> FIRSTNAME = DONALD
>
> -----Original Message-----
> From: Adrian Lynch [mailto:[EMAIL PROTECTED]
> Sent: Thursday, February 24, 2005 9:55 AM
> To: CF-Talk
> Subject: RE: OT-SQL matching
>
> In fact I think you're on to a hiding to nothing...
>
> ....... I don't think you can make any reasonable assumptions about what is a
> middle name and what is a suffix. In the before samples VICTOR could be a
> suffix.
>
> Maybe I'm not understanding it all but I don't think you'll be able to do
> this with 100% accuracy.
>
> Ade
>
> -----Original Message-----
> From: Adrian Lynch [mailto:[EMAIL PROTECTED]
> Sent: 24 February 2005 15:49
> To: CF-Talk
> Subject: RE: OT-SQL matching
>
> Will the last name always be first in the column? In the samples below, is
> the person's first name DONALD, and then last names LITTLE, LITTLE VICTOR
> and LITTLE Victor?
>
> LITTLE DONALD V
> LITTLE DONALD VICTOR
> LITTLE DONALD Victor JR
>
> Ade
>
> -----Original Message-----
> From: Eric Creese [mailto:[EMAIL PROTECTED]
> Sent: 24 February 2005 15:37
> To: CF-Talk
> Subject: RE: OT-SQL matching
>
> Okay I go something to work so my orginal question is taken care of. However
> I have another question that deals with this sort of thing. I am now looking
> at another datasource that I am going to have to match on at somepoint later
> but they have a single name field where a persons name is one field and is
> LASTNAME FIRSTNAME, MIDDLE SUFFIX (LITTLE DONALD V or LITTLE DONALD VICTOR
> or LITTLE DONALD Victor JR).
>
> I am simply querying out the information that I need and putting it in a
> holding table. But while I am doing this I want to extract the LAST and
> FIRST names and store them in their seperate columns in the holding the
> table. I can get the last name and I can get the first name but along with
> the first name I get the middle name and suffix as well. By the way this is
> a data file provided by the FAA. Here is what I am doing lease the INTO
> statement.
>
> SELECT
> SUBSTRING([name], 1, CHARINDEX(' ', [name])) AS lastname,
> SUBSTRING([name], CHARINDEX(' ',[name])+1,LEN([name])) as firstname ,
> street,street2,city,state,[zip code] as zip
> FROM master
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Logware (www.logware.us): a new and convenient web-based time tracking
application. Start tracking and documenting hours spent on a project or with a
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:196340
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
