How to select all of the name before and?

[DarkWorlds]

Ok I had a question on something. I have a field with names in it, and sometimes it has two.

AKA

Bob and Jill

Well I just want to select the name before the "and". How would I go about doing this? Also the "and" could also be "&".

I look forward to your reply

 

[bborissov]

SELECT CASE WHEN CHARINDEX('and', YourField) > 0
            THEN LEFT(YourField, CHARINDEX('and', YourField)-1)
            WHEN CHARINDEX('&', YourField) > 0
            THEN LEFT(YourField, CHARINDEX('&', YourField)-1)
            ELSE YourField END AS YourField

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[DarkWorlds]

That worked well for me, but how would I also do it from the right, found something new I have to eleminate

 

[DarkWorlds]

N/M got it