Converting two worded string to one word only 1

[Bwintech]

Hi,

Many thanks for your previous help. I have now come across a string of student names and surnames but I want to cut that to only be names. For example:

name = john doe

I want to run an update to let name = John

How can I do this - look for whitespace and then include everything in the position -1?

Many thanks for the assistance, much appreciated

 

[mukund]

Hi Bwintech,
You can first backup the database and then run the following Update query on the students table

Update students SET name = ltrim(rtrim(left(name, charindex(' ',name,1))))

Here students is the table & name is the column that needs to be updated.

Hope this helps,
Mukund.

 

[bperry]

That solution is fine, but would bomb if there wasa case were no space was found in the name. (Say there was accidentally JohnDoe or soemthing like that.) This version fixes that, if necessary.

Update Students set name =
select substring(name,1,
CASE when charindex(' ',name,1) > 0 then charindex(' ',name,1) -1 else LEN(name) END
)as start
from Students

 

[bperry]

Oooops, pardon my syntax!
Here is a correct version:

Update Students set name =
substring
(name,
1,
CASE when charindex(' ',name,1) > 0 then charindex(' ',name,1) -1 else LEN(name) END
)
------------------
sorry 'bout that

 

[moonshadow]

You can lose the CASE statement thus:
Update Students set name =
substring
(name,
1,
charindex(' ',name + ' ',1)-1
)

 

[bperry]

I like that.
bp