What is the equivalent of Firebirds "STARTING WITH" when using other databases e.g. Oracle?

Question

I am investigating the possibility of updating our application to work with database servers other than Firebird. We rely on "STARTING WITH" for accessing our hierarchical data. Without it, I don't see how we can migrate to another database without some serious redesign.

If you are not familiar with STARTS WITH, it simply checks to see if a string field starts with a particular string e.g. ... WHERE 'This is a test' STARTS WITH 'This is' ... would return true. If a column is indexed, the index will be used for the comparison.

Do other database servers (especially Oracle/MSSQL) support "STARTING WITH" (or "STARTS WITH")?

Answer 1

The standard SQL to achieve that is something like ... WHERE 'This is a test' like 'This is%';

Answer 2

Some DB's also support WHERE LEFT('This is a test', 7) = 'This is' (although I don't remember whether this is part of the SQL standard).

Answer 3

Oracle's Select has a "Start With" http://www.oracle.com/pls/db10g/db10g.show_toc?which=main&partno=b10759&maxlevel=2&section=&expand=41274">Oracle Documentation 10g for hierarchial queries. It's been part of Oracle for many years (Oracle 8 at least).

Not sure about SQL Server.