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

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