Convert SUBSTR from Oracle to SQL Server

Oracle SUBSTR function is used to extract substring from the string starting from the given position:

SQL> SELECT SUBSTR('This is a text', 3, 5) FROM dual;

SUBST
-----
is is

The third argument specifies the length of substring. If it is missed, the substring is extracted until the end of the string:

SQL> SELECT SUBSTR('This is a text', 11) FROM dual;

SUBS
----
text

In case of a negative start position -N is specified, the function returns last N characters:

SQL> SELECT SUBSTR('This is a text', -4) FROM dual;

SUBS
----
text

SQL Server provides function SUBSTRING having similar capabilities, except it does not allow a negative start position and always requires the substring length specified.

To extract the substring until the end of the string in MS SQL, the following workaround can be used:

1> SELECT SUBSTRING('This is a text', 11, LEN('This is a text')) AS res
2> GO
res
--------------
text

Oracle SUBSTR call with negative start position can be converted into MS SQL RIGTH function as follows:

1> SELECT RIGHT('This is a text', 4) AS res
2> GO
res
--------------
text

Remember that while the basic functionality of extracting substrings is similar in Oracle and SQL Server, there might be other differences to consider, such as case sensitivity or collation settings, depending on your specific conversion requirements. It's always a good practice to thoroughly test the converted code and ensure the desired results are achieved after the migration.

Specialists of Intelligent Converters offer Database Migration Service to help you migrate SQL and PL/SQL from Oracle to SQL Server or Azure SQL.