https://sqlines.com/ 2026-04-29T21:37:01+00:00 https://sqlines.com/ https://sqlines.com/lib/images/favicon.ico text/html 2012-02-02T14:08:16+00:00 https://sqlines.com/oracle/functions/cast?rev=1328191696&do=diff CAST function converts one built-in data type into another built-in data type with the specified precision and length. Quick Example: Convert a string value to NUMBER leaving 2 digits after the decimal point: SELECT CAST('245.211' AS NUMBER(5,2)) FROM dual; -- Result: 245.21 text/html 2012-09-23T22:52:57+00:00 https://sqlines.com/oracle/functions/empty_blob?rev=1348440777&do=diff In Oracle, EMPTY_BLOB() function allows you to assign an empty value (with 0 length) to a BLOB column. Although the length is 0, the value is not NULL. In SQL Server, you can use 0x constant (empty binary string). Using EMPTY_BLOB() in Oracle Let's create a table with a BLOB column in Oracle and insert data: text/html 2012-09-23T22:52:14+00:00 https://sqlines.com/oracle/functions/empty_clob?rev=1348440734&do=diff In Oracle, EMPTY_CLOB() function allows you to assign an empty string (with 0 length) to a CLOB or NCLOB column. Although the length is 0, the value is not NULL. In SQL Server, you can use '' constant (empty string). Using EMPTY_CLOB() in Oracle Let's create a table with a CLOB and NCLOB column in Oracle and insert data: text/html 2012-07-24T07:45:57+00:00 https://sqlines.com/oracle/functions/initcap?rev=1343115957&do=diff In Oracle, INITCAP function capitalizes the first letter of each word and lowercases all other letters in a string. Words are delimited by a space or any other non-alphanumeric letter. In SQL Server you can use a user-define function (UDF) to implement this functionality (see below). text/html 2017-10-12T20:16:58+00:00 https://sqlines.com/oracle/functions/instr?rev=1507839418&do=diff In Oracle, INSTR function returns the position of a substring in a string, and allows you to specify the start position and which occurrence to find. In SQL Server, you can use CHARINDEX function that allows you to specify the start position, but not the occurrence, or you can use a user-defined function. text/html 2012-01-11T14:07:27+00:00 https://sqlines.com/oracle/functions/last_day?rev=1326290847&do=diff LAST_DAY function returns date (not just day) where the day is set to the last day of the month. Quick Example: Get the last date of the current month (SYSDATE returns January 11, 2012 in this example): SELECT LAST_DAY(SYSDATE) FROM dual; -- Result: January 31, 2012 text/html 2012-07-19T10:18:11+00:00 https://sqlines.com/oracle/functions/length?rev=1342693091&do=diff In Oracle, LENGTH function returns the length of a string in characters as defined by the input character set. In SQL Server, you can use LEN function, but note that it excludes trailing blanks. When applied to a CHAR or NCHAR column, Oracle LENGTH returns the maximum length of the column (defined in CREATE TABLE), while SQL Server LEN returns the actual data length. text/html 2012-08-15T09:23:23+00:00 https://sqlines.com/oracle/functions/lpad?rev=1345022603&do=diff In Oracle, LPAD function left-pads a string to the specified length with the specified characters. Note that the string is truncated if it already exceeds the specified length. In SQL Server, you can use an expression using RIGTH, REPLICATE and LEFT functions to get the same result as Oracle. text/html 2012-01-12T08:21:39+00:00 https://sqlines.com/oracle/functions/months_between?rev=1326356499&do=diff MONTHS_BETWEEN function returns the number of months between 2 dates. Quick Example: Get the number of months between February 10, 2012 and January 01, 2012: SELECT MONTHS_BETWEEN (DATE '2012-02-10', DATE '2012-01-01') FROM dual; -- Result: 1.29032258 text/html 2012-09-22T15:15:11+00:00 https://sqlines.com/oracle/functions/nvl?rev=1348326911&do=diff In Oracle, NVL(exp1, exp2) function accepts 2 expressions (parameters), and returns the first expression if it is not NULL, otherwise NVL returns the second expression. In SQL Server, you can use ISNULL(exp1, exp2) function. Oracle Example: -- Return 'N/A' if name is NULL SELECT NVL(name, 'N/A') FROM countries; text/html 2012-08-17T22:10:59+00:00 https://sqlines.com/oracle/functions/translate?rev=1345241459&do=diff In Oracle, TRANSLATE function allows you to perform one-to-one, single character substitution in a string. In SQL Server, you can use REPLACE function to replace each character or an user-defined function. Oracle: -- Replace # and blank with _ SELECT TRANSLATE('Unit Number#2', '# ', '__') FROM dual; # Unit_Number_2 text/html 2013-02-11T10:33:06+00:00 https://sqlines.com/oracle/functions/trunc_datetime?rev=1360578786&do=diff In Oracle, TRUNC(datetime, unit) function allows you to truncate a datetime value to the specified unit (set zero time, set the first day of the month i.e). Oracle: -- Get current datetime with the time set to zero (returns: 2012-07-13 00:00:00) SELECT TRUNC(SYSDATE) FROM dual; text/html 2012-07-17T21:16:07+00:00 https://sqlines.com/oracle/functions/unistr?rev=1342559767&do=diff In Oracle, UNISTR function converts a string literal containing Unicode code points represented as '\hhhh' (hhhh is a hex value) as well as regular characters to Unicode string. In SQL Server, you can use an expression using NCHAR function and N'string' literals.