extract portion of a string using SQL

[Guest]

If I have a column containing one or more first names eg Bill Andrew Mike and
I want to extract the first name in the column (eg Bill) I can use SQL code -
Left([names]),Instr(1,[Names]," ")-1) however if the row only contains one
name the query returns an error for that row. Do I need to add code at the
end of this expression so that if the row only contains one name the
extression returns that name?

 

[david epsom dot com dot au]

Left([names]),Instr(1,[Names]," ")-1) however if the row

Left([names]),Instr(1,[Names]& " "," ")-1)

Or you could use IIF

IIF(Instr(1,[Names]," "),Left([names]),Instr(1,[Names]," ")-1),[names])

--which is easier to understand, but harder to read.

(david)

 

[Duane Hookom]

Try add a space to Names:
Left([names]),Instr(1,[Names] & " "," ")-1)

 

[John Spencer (MVP)]

Try

TRIM(Left([names],Instr(1,[Names] & " "," ")-1))

OR

IIF(Instr(1,[Names]," ")=0,[Names],Left([names],Instr(1,[Names]," ")-1))

 

[John Vinson]

If I have a column containing one or more first names eg Bill Andrew Mike and
I want to extract the first name in the column (eg Bill) I can use SQL code -
Left([names]),Instr(1,[Names]," ")-1) however if the row only contains one
name the query returns an error for that row. Do I need to add code at the
end of this expression so that if the row only contains one name the
extression returns that name?

Use IIF:

IIF(InStr([names], " ") = 0, [names], Left([names]),Instr(1,[Names],"
")-1))

John W. Vinson[MVP]

 

[John Vinson]

Try add a space to Names:
Left([names]),Instr(1,[Names] & " "," ")-1)

<boink>

Clever Duane. Noted...

John W. Vinson[MVP]

 

[Duane Hookom]

I thought it clever also. Apparently David and John Spencer are at least a
clever.

--
Duane Hookom
MS Access MVP
--

Try add a space to Names:
Left([names]),Instr(1,[Names] & " "," ")-1)

<boink>

Clever Duane. Noted...

John W. Vinson[MVP]