Ask the Expert

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

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: