How do you search a field for a value, ex: search for "a" in Happy

[Tobbie]

How do a search a field that contains firstname and lastname for a space which seperates the two. For example the field "Name" contains the string "John Doe". I need a sql stmt that will search "John Doe" for the space. Also, in the result I would like to split the column "Name" into two result columns called "FirstName" and "LastName". I do not want to modify the table in anyway all I just want the result from the query to be split into two columns "FirstName" and "LastName". Basically, what I have to do is write a report sorted by last name, so I need to extract the last name from the field. Hope I explained that well enough. Any help anyone could provide would be greatly appreciated. Thank you.

 

[Flem]

Hi,

Not sure that this will help but to get this done and me being a bit new to this area
I would simply bcp out the table and import it to Excel, you can easily parse the field into the two fields and bcp it back in..

bcp DbName.Table out table.txt -c -Uuser -Ppassword -Sserver

Then in excel import in the file and use the space between first name and surname as a delimiter, this will put the 2 columns into a seperate column and save the file as a txt file and bcp it back into the database.

PF.

 

[ETasse]

This code snippet does what you want:

DECLARE @fullname VARCHAR(40) 
SELECT @fullname = "John Doe"
SELECT RTRIM(SUBSTRING(@fullname,1,
             CHARINDEX(" ",@fullname))) 'first',
       LTRIM(SUBSTRING(@fullname,
             CHARINDEX(" ",@fullname), 
             DATALENGTH(@fullname))) 'last'