Q

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?

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

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close