Database Reference
In-Depth Information
Figure 9.13
Extract the First
Name from a
Column
Containing First
and Last Names.
SELECT NAME, LENGTH(NAME), INSTR(NAME,' ')+1
, SUBSTR(NAME,(INSTR(NAME,' ')+1)) LASTNAME
FROM ARTIST;
This next example uses several functions. The PLAYING_TIME col-
umn contains the minutes and seconds of playing time for a song in the for-
mat m:ss (minutes:seconds). This query is built gradually in sections. First,
find the colon and separate minutes from seconds using the INSTR and
SUBSTR functions. Extract the minutes:
SUBSTR(PLAYING_TIME,1,INSTR(PLAYING_TIME,':')-1)
Extract the seconds:
SUBSTR(PLAYING_TIME,INSTR(PLAYING_TIME,':')+1)
