Substring function
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?

    Requires Free Membership to View

    Login

    By submitting your registration information to SearchOracle.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchOracle.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

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


LEFT(string, 8)

because the LEFT function, in those databases that support it, starts at position 1.

For More Information


This was first published in September 2002

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top