I want to get the first eight characters of a text field, but I also need to get more values for other fields of three tables. I don't know what form of the "substring" function is correct to use. Or is there another function that I could use?
The standard SQL substring function is
SUBSTRING(string FROM start FOR length)
Microsoft SQL/Server, Sybase, MySQL and PostgreSQL all support this syntax. Other databases may also support it (I have not done an exhaustive survey). Double-check by looking it up in your database documentation.
In Microsoft SQL/Server, Sybase, and MySQL, you can also use
SUBSTRING(string, start, length)
In Microsoft Access and MySQL, you can use
MID(string, start, length)
In Oracle, use
SUBSTR(string, start, length)
A good reference for SQL functions is Chapter 4, SQL Functions, the sample chapter of O'Reilly's book SQL in a Nutshell by Kevin Kline and Daniel Kline (ISBN 1-56592-744-3).
For your specific requirement, you would write
SUBSTRING(string FROM 1 FOR 8)
Depending on your database, you might also be able to write
because the LEFT function, in those databases that support it, starts at position 1.
For More Information
- Dozens more answers to tough SQL questions from Rudy Limeback.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.