extract portion of a string using SQL

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
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)
 
Try

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

OR

IIF(Instr(1,[Names]," ")=0,[Names],Left([names],Instr(1,[Names]," ")-1))
 
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?
Click to expand...

Use IIF:

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

John W. Vinson[MVP]
 
I thought it clever also. Apparently David and John Spencer are at least a
clever.

--
Duane Hookom
MS Access MVP
--

John Vinson said:
Try add a space to Names:
Left([names]),Instr(1,[Names] & " "," ")-1)
Click to expand...

<boink>

Clever Duane. Noted...

John W. Vinson[MVP]
Click to expand...
 
Back
Top