I want to get the first eight characters of a text field, but I also need to get more values for other fields of...
By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
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.
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.