Shortening a row of information. (1 Viewer)

[New2SQL]

I have got a set of information that has a mixed bag of names. Some have only a first name but some have a middle name also.

I am looking to shortern the information just to the firstname (i.e. in stead of the results showing "Bill William" as the firstname, i want to just show "Bill").

i tried using the Left function but because the strings are of varied length i need the cut off to be the space between the various names.

Any help would be much appreciated.

 

[RV]

Use a combination of the Left and the InStr function:

Left(columnname, Instr(columnname, " ")-1)

RV

 

[New2SQL]

works a treat but...

...in the row i used this on, some names have no spaces so literally just first name and in the results i get from the expression, i get an #ERROR for those people.

is there a way to get the results from that expression as well as keeping those unaffected as they were?

cheers.

 

[workmad3]

Iff (InStr(columnname, " "), Left(columnname, Instr(columnname, " ")-1), columnname)

should work (assuming my Iff syntax is correct)

 

[RV]

should work (assuming my Iff syntax is correct)

Close
Correct syntax is

Iff (InStr(columnname, " ") = 0, columnname, 
Left(columnname, Instr(columnname, " ")-1))

RV