Data type formatting functions v16

The EDB Postgres Advanced Server formatting functions provide a powerful set of tools for converting various data types (date/time, integer, floating point, numeric) to formatted strings and for converting from formatted strings to specific data types. These all functions follow a common calling convention: the first argument is the value to format and the second argument is a string template that defines the output or input format.

Overview of data type formatting functions

FunctionReturn typeDescriptionExampleResult
TO_BLOB(raw)BLOBConvert a RAW value to BLOB value.TO_BLOB('abc')\x616263
TO_CLOB(string)CLOBConvert a CHAR, VARCHAR, VARCHAR2, NCHAR, NVARCHAR2, or CLOB values to CLOB values.TO_CLOB('aaaa')aaaa
TO_CHAR(DATE [, format ])VARCHAR2Convert a date/time to a string with output, format. The default format is DD-MON-YY.TO_CHAR(SYSDATE, 'MM/DD/YYYY HH12:MI:SS AM')07/25/2007 09:43:02 AM
TO_CHAR(TIMESTAMP [, format ])VARCHAR2Convert a timestamp to a string with output, format. The default format is DD-MON-YY.TO_CHAR (CURRENT_TIMESTAMP, 'MM/DD/YYYY HH12:MI:SS AM')08/13/2015 08:55:22 PM
TO_CHAR(INTEGER [, format ])VARCHAR2Convert an integer to a string with output, formatTO_CHAR(2412, '999,999S')2,412+
TO_CHAR(NUMBER [, format ])VARCHAR2Convert a decimal number to a string with output, formatTO_CHAR(10125.35, '999,999.99')10,125.35
TO_CHAR(DOUBLE PRECISION, format)VARCHAR2Convert a floating-point number to a string with output, formatTO_CHAR (CAST(123.5282 AS REAL), '999.99')123.53
TO_DATE(string [, format ])TIMESTAMPConvert a date or timestamp formatted string to a TIMESTAMP data typeTO_DATE('2007-07-04 13:39:10', 'YYYY-MM-DD HH24:MI:SS')

TO_DATE('2007-07-04','YYYY-MM-DD')
04-JUL-07 13:39:10

04-JUL-07 00:00:00
TO_DSINTERVAL(string)INTERVAL DAY TO SECONDConvert a character string of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to an INTERVAL DAY TO SECOND type.TO_DSINTERVAL('80 13:30:00')80 days 13:30:00
TO_NCHAR(string)NVARCHAR2Convert a character string, CHAR, VARCHAR2, CLOB, or NCLOB value to the national character set.TO_NCHAR('test')test
TO_NCHAR(number [, format])NVARCHAR2Convert a number formatted string to a national character data type.TO_NCHAR(7654321, 'C9G999G999D99')7,654,321.00
TO_NCHAR(DATE [, format ])NVARCHAR2Convert a date/time to a formatted string of national character data type.TO_NCHAR(timestamp '2022-04-20 17:31:12.66', 'Day: MONTH DD, YYYY')Wednesday: APRIL 20, 2022
TO_NUMBER(string [, format ])NUMBERConvert a number formatted string to a NUMBER data type.TO_NUMBER('2,412-', '999,999S')-2412
TO_TIMESTAMP(string, format)TIMESTAMPTZConvert a timestamp formatted string to a TIMESTAMP WITH TIME ZONE data type.TO_TIMESTAMP('05 Dec 2000 08:30:25 pm', 'DD Mon YYYY hh12:mi:ss pm')05-DEC-00 20:30:25 +05:30
TO_TIMESTAMP_TZ(string[, format])TIMESTAMPTZConvert a timestamp formatted string to a TIMESTAMP WITH TIME ZONE data type.TO_TIMESTAMP_TZ ('2003/12/13 10:13:18 -8:00', 'YYYY/MM/DD HH:MI:SS TZH:TZM')13-DEC-03 23:43:18 +05:30
FROM_TZ(timestamp_value, timezone_value)TIMESTAMPTZConvert a timestamp value and a timezone to a TIMESTAMP WITH TIME ZONE value.FROM_TZ(TIMESTAMP '2017-08-08 08:09:10', 'Asia/Kolkata')08-AUG-17 08:09:10 +05:30

Available template patterns for date values

This table shows the template patterns available for formatting date values using the TO_CHAR, TO_DATE, TO_TIMESTAMP, and TO_TIMESTAMP_TZ functions.

PatternDescription
HHHour of day (01-12)
HH12Hour of day (01-12)
HH24Hour of day (00-23)
MIMinute (00-59)
SSSecond (00-59)
SSSSSSeconds past midnight (0-86399)
FFnFractional seconds where n is an optional integer from 1 to 9 for the number of digits to return. The default is 6.
AM or A.M. or PM or P.M.Meridian indicator (uppercase)
am or a.m. or pm or p.m.Meridian indicator (lowercase)
Y,YYYYear (4 and more digits) with comma
YEARYear (spelled out)
SYEARYear (spelled out) (BC dates prefixed by a minus sign)
YYYYYear (4 and more digits)
SYYYYYear (4 and more digits) (BC dates prefixed by a minus sign)
YYYLast 3 digits of year
YYLast 2 digits of year
YLast digit of year
IYYYISO year (4 and more digits)
IYYLast 3 digits of ISO year
IYLast 2 digits of ISO year
ILast 1 digit of ISO year
BC or B.C. or AD or A.D.Era indicator (uppercase)
bc or b.c. or ad or a.d.Era indicator (lowercase)
MONTHFull uppercase month name
MonthFull mixed-case month name
monthFull lowercase month name
MONAbbreviated uppercase month name (3 chars in English, localized lengths vary)
MonAbbreviated mixed-case month name (3 chars in English, localized lengths vary)
monAbbreviated lowercase month name (3 chars in English, localized lengths vary)
MMMonth number (01-12)
DAYFull uppercase day name
DayFull mixed-case day name
dayFull lowercase day name
DYAbbreviated uppercase day name (3 chars in English, localized lengths vary)
DyAbbreviated mixed-case day name (3 chars in English, localized lengths vary)
dyAbbreviated lowercase day name (3 chars in English, localized lengths vary)
DDDDay of year (001-366)
DDDay of month (01-31)
DDay of week (1-7; Sunday is 1)
WWeek of month (1-5); the first week starts on the first day of the month
WWWeek number of year (1-53); the first week starts on the first day of the year
IWISO week number of year; the first Thursday of the new year is in week 1
CCCentury (2 digits); the 21st century starts on 2001-01-01
SCCSame as CC except BC dates are prefixed by a minus sign
JJulian Day (days since January 1, 4712 BC)
QQuarter
RMMonth in Roman numerals (I-XII; I=January) (uppercase)
rmMonth in Roman numerals (i-xii; i=January) (lowercase)
RRFirst 2 digits of the year when given only the last 2 digits of the year. Result is based upon an algorithm using the current year and the given 2-digit year. The first 2 digits of the given 2-digit year are the same as the first 2 digits of the current year with the following exceptions: If the given 2-digit year is <50 and the last 2 digits of the current year is >= 50, then the first 2 digits for the given year is 1 greater than the first 2 digits of the current year. If the given 2-digit year is >= 50 and the last 2 digits of the current year is <50, then the first 2 digits for the given year is 1 less than the first 2 digits of the current year.
RRRRAffects only TO_DATE function. Allows specification of 2-digit or 4-digit year. If 2-digit year given, then returns first 2 digits of year like RR format. If 4-digit year given, returns the given 4-digit year.
TZHTime-zone hours
TZMTime-zone minutes

Date and time modifiers

You can apply certain modifiers to any template pattern to alter its behavior. For example, FMMonth is the Month pattern with the FM modifier. This table shows the modifier patterns for date/time formatting.

ModifierDescriptionExample
FM prefixFill mode (suppress padding blanks and zeros)FMMonth
TH suffixUppercase ordinal number suffixDDTH
th suffixLowercase ordinal number suffixDDth
FX prefixFixed format global option (see usage notes)FX Month DD Day
SP suffixSpell modeDDSP

Usage notes for date/time formatting:

  • FM suppresses leading zeroes and trailing blanks that are otherwise added to make the output of a pattern fixed width.

  • TO_TIMESTAMP, TO_TIMESTAMP_TZ, and TO_DATE skip multiple blank spaces in the input string if you don't use the FX option. You must use FX as the first item in the template. For example:

    TO_TIMESTAMP('2000 - JUN', 'YYYY-MON') is correct, but
    TO_TIMESTAMP('2000    JUN', 'FXYYYY MON') and
    TO_TIMESTAMP_TZ('2000    JUN', 'FXYYYY MON') returns an error
    because TO_TIMESTAMP and TO_TIMESTAMP_TZ expects one space
    only.
  • Ordinary text is allowed in TO_CHAR templates and is output literally.

  • In conversions from string to timestamp, timestamptz, or date, the CC field is ignored if there is a YYY, YYYY, or Y,YYY field. If CC is used with YY or Y, then the year is computed as (CC-1)*100+YY.

This table shows some examples of the use of the TO_CHAR and TO_DATE functions:

ExpressionResult
TO_CHAR(CURRENT_TIMESTAMP, 'Day, DD  HH12:MI:SS')'Tuesday  , 06  05:39:18'
TO_CHAR(CURRENT_TIMESTAMP, 'FM-Day, FMDD  HH12:MI:SS')'Tuesday, 6  05:39:18'
TO_CHAR(-0.1, '99.99')'  -.10'
TO_CHAR(-0.1, 'FM9.99')'-.1'
TO_CHAR(0.1, '0.9')' 0.1'
TO_CHAR(12, '9990999.9')'    0012.0'
TO_CHAR(12, 'FM9990999.9')'0012.'
TO_CHAR(485, '999')' 485'
TO_CHAR(-485, '999')'-485'
TO_CHAR(1485, '9,999')' 1,485'
TO_CHAR(1485, '9G999')' 1,485'
TO_CHAR(148.5, '999.999')' 148.500'
TO_CHAR(148.5, 'FM999.999')'148.5'
TO_CHAR(148.5, 'FM999.990')'148.500'
TO_CHAR(148.5, '999D999')' 148.500'
TO_CHAR(3148.5, '9G999D999')' 3,148.500'
TO_CHAR(-485, '999S')'485-'
TO_CHAR(-485, '999MI')'485-'
TO_CHAR(485, '999MI')'485 '
TO_CHAR(485, 'FM999MI')'485'
TO_CHAR(-485, '999PR')'<485>'
TO_CHAR(485, 'L999')'$ 485'
TO_CHAR(485, 'RN')'        CDLXXXV'
TO_CHAR(485, 'FMRN')'CDLXXXV'
TO_CHAR(5.2, 'FMRN')'V'
TO_CHAR(12, '99V999')' 12000'
TO_CHAR(12.4, '99V999')' 12400'
TO_CHAR(12.45, '99V9')' 125'

This table shows some examples of the use of the TO_TIMESTAMP_TZ function:

ExpressionResult
TO_TIMESTAMP_TZ('12-JAN-2010', 'DD-MONTH-YYYY')'12-JAN-10 00:00:00 +05:30'
TO_TIMESTAMP_TZ('03-APR-07 09:12:21 P.M','DD-MON-YY HH12:MI:SS A.M')'03-APR-07 09:12:21 +05:30'
TO_TIMESTAMP_TZ('2003/12/13 10:13:18 -8:00', 'YYYY/MM/DD HH:MI:SS TZH:TZM')'13-DEC-03 23:43:18 +05:30'
TO_TIMESTAMP_TZ('20-MAR-20 04:30:00 +08:00', 'DD-MON-YY HH:MI:SS TZH:TZM')'20-MAR-20 02:00:00 +05:30'
TO_TIMESTAMP_TZ('10-Sep-02 14:10:10.123000', 'DD-MON-RR HH24:MI:SS.FF')'10-SEP-02 14:10:10.123 +05:30'
TO_TIMESTAMP_TZ('20-MAR-20 04:30:00.123456 PM +03:00')'20-MAR-20 09:30:00.123456 -04:00'

Available template patterns for numeric values

This table lists the template patterns available for formatting numeric values:

PatternDescription
9Value with the specified number of digits
0Value with leading zeroes
. (period)Decimal point
, (comma)Group (thousand) separator
$Dollar sign
SSign anchored to number (uses locale).
LCurrency symbol (uses locale)

The pattern 9 results in a value with the same number of digits as there are 9s. If a digit isn't available, the server ignores the corresponding 9s. The S pattern doesn't support +, and the $ pattern doesn't support decimal points in the expression.

This table shows some examples of the use of the TO_NUMBER function:

ExpressionResult
TO_NUMBER('-65', 'S99')' -65'
TO_NUMBER('$65', 'L99')' 65'
TO_NUMBER('9678584', '9999999')' 9678584'
TO_NUMBER('123,456,789', '999,999,999')' 123456789'
TO_NUMBER('1210.73', '9999.99')' 1210.73'
TO_NUMBER('1210.73')' 1210.73'
TO_NUMBER('0101.010','FM99999999.99999')' 101.010'

Numeric modifiers

This table shows the modifier pattern for numeric formatting:

PatternDescriptionExample
FM prefixFill mode (suppress trailing zeroes and padding blanks)FM99.99

Output formats for string and date functions

In an output template string (for TO_CHAR), certain patterns are recognized and replaced with appropriately formatted data from the value to format. Any text that isn't a template pattern is copied verbatim. Similarly, in an input template string (for anything but TO_CHAR), template patterns identify the parts of the input data string to look at and the values to find there.

If you don't specify a date, month, or year when calling TO_TIMESTAMP, TO_TIMESTAMP_TZ, or TO_DATE, then by default the output format considers the first date of a current month or current year. In the following example, date, month, and year isn't specified in the input string. TO_TIMESTAMP, TO_TIMESTAMP_TZ, and TO_DATE returns a default value of the first date of a current month and current year.

edb=# select to_timestamp('12', 'HH');
Output
      to_timestamp
---------------------------
 01-MAY-20 12:00:00 +05:30
(1 row)
edb=# select to_timestamp_tz('12', 'HH');
Output
      to_timestamp_tz
---------------------------
 01-MAY-20 12:00:00 +05:30
(1 row)
edb=# select to_date('12', 'HH');
Output
      to_date
--------------------
 01-MAY-20 12:00:00
(1 row)

Altering the output format

Alter the output format of TO_DSINTERVAL(string) using the intervalstyle GUC setting. For example:

edb=# SET intervalstyle = 'sql_standard';
SET

edb=# select to_dsinterval('80 13:30:00') from dual;
Output
to_dsinterval 
---------------
80 13:30:00
edb=# SET intervalstyle = 'postgres_verbose';
SET
edb=# select to_dsinterval('80 13:30:00') from dual;
Output
to_dsinterval        
----------------------------
@ 80 days 13 hours 30 mins

Creating an index with the IMMUTABLE form of the TO_CHAR function

Certain cases of the TO_CHAR function results in usage of an IMMUTABLE form of the function. A function is IMMUTABLE if the function doesn't modify the database, and the function returns the same, consistent value dependent upon only its input parameters. That is, the settings of configuration parameters, the locale, the content of the database, and so on don't affect the results returned by the function.

For more information about function volatility categories VOLATILE, STABLE, and IMMUTABLE, see the function volatility doc.

A particular advantage of an IMMUTABLE function is that you can use it in the CREATE INDEX command to create an index based on that function.

For the TO_CHAR function to use the IMMUTABLE form, you must satisfy these conditions:

  • The first parameter of the TO_CHAR function must be of data type TIMESTAMP.
  • The format specified in the second parameter of the TO_CHAR function must not affect the return value of the function based on factors such as language and locale. For example, you can use a format of 'YYYY-MM-DD HH24:MI:SS' for an IMMUTABLE form of the function since the result of the function is the date and time expressed solely in numeric form, regardless of locale settings. However, you can't use a format of 'DD-MON-YYYY' for an IMMUTABLE form of the function because the three-character abbreviation of the month can return different results depending upon the locale setting.

Format patterns that result in a non-immutable function include any variations of spelled out or abbreviated months (MONTH, MON), days (DAY, DY), median indicators (AM, PM), or era indicators (BC, AD).

For this example, a table with a TIMESTAMP column is created:

CREATE TABLE ts_tbl (ts_col TIMESTAMP);

This example shows the successful creation of an index with the IMMUTABLE form of the TO_CHAR function.

edb=# CREATE INDEX ts_idx ON ts_tbl (TO_CHAR(ts_col,'YYYY-MM-DD HH24:MI:SS'));
CREATE INDEX
edb=# \dS ts_idx
Output
Index "public.ts_idx"
 Column  |       Type        |                         Definition
---------+-------------------+------------------------------------------------------------
 to_char | character varying | to_char(ts_col, 'YYYY-MM-DD HH24:MI:SS'::character varying)
btree, for table "public.ts_tbl"

This results in an error because the format specified in the TO_CHAR function prevents the use of the IMMUTABLE form since the three-character month abbreviation, MON, can result in different return values based on the locale setting.

edb=# CREATE INDEX ts_idx_2 ON ts_tbl (TO_CHAR(ts_col, 'DD-MON-YYYY'));
ERROR:  functions in index expression must be marked IMMUTABLE

Difference TO_CHAR, TO_NUMBER, and SUBSTR functions

Initialize the database using the INITDBOPTS variable to create clusters in a mode compatible with Oracle databases. The clusters created in PostgreSQL mode don't include compatibility features. To create a new cluster in PostgreSQL mode, use the "--no-redwood-compat" option, which offers a similar behavior to PostgreSQL.

A SELECT statement that contains one of the functions shown in the following table returns the indicated result in EDB Postgres Advanced Server and PostgreSQL. Listed are the following differences for TO_CHAR, TO_NUMBER, and SUBSTR functions.

TO_CHAR: Converts the timestamp to string according to a given format.

Note
  • The 'syear', 'syyyy', 'year', 'rrrr', 'sp', 'spth', 'scc', 'rr', 'ff', 'ff7', 'ff8', 'ff9', and lowercase 'of', 'tzh', and 'tzm' patterns aren't supported in PostgreSQL.
  • 'TH' modifier adds fewer padding zeros in EDB Postgres Advanced Server.
  • The upper case 'TH' pattern, which displays the suffix in uppercase, isn't supported in EDB Postgres Advanced Server. If you specify the 'TH/th' pattern, the suffix is always displayed in lower case.
  • A modifier can appear in an 'FM' pattern, and each occurrence toggles the modifier's effect. To toggle the effect of the 'FM' pattern, specify it an even number of times to turn off the effect and specify it an odd number of times to turn on the effect of the 'FM' pattern in PostgreSQL. However, in EDB Postgres Advanced Server, specifying the 'FM' pattern always turns on the effect of the 'FM' pattern.

This table shows the examples for TO_CHAR(timestamp, text) function:

ExamplesResult
EDB Postgres Advanced ServerPostgreSQL
SELECT TO_CHAR(current_timestamp, 'of');

SELECT TO_CHAR(current_timestamp, 'OF');
+05:30of

+05:30
SELECT TO_CHAR(current_timestamp, 'syear');twenty twenty ones1ear
SELECT TO_CHAR(current_timestamp, 'syyyy');2021s2021
SELECT TO_CHAR(current_timestamp, 'year');twenty twenty one1ear
SELECT TO_CHAR(current_timestamp, 'rrrr');2021rrrr
SELECT TO_CHAR(current_timestamp, 'hhsp');five05sp
SELECT TO_CHAR(current_timestamp, 'hhspth');fifth05spth
SELECT TO_CHAR(current_timestamp, 'scc');21s21
SELECT TO_CHAR(current_timestamp, 'rr');21rr
SELECT TO_CHAR(current_timestamp, 'ff');923202ff
SELECT TO_CHAR(current_timestamp, 'ff7');4192310ff7
SELECT TO_CHAR(current_timestamp, 'ff8');36181700ff8
SELECT TO_CHAR(current_timestamp, 'ff9');640405000ff9
SELECT TO_CHAR(current_timestamp, 'tzh');

SELECT TO_CHAR(current_timestamp, 'TZH');
+05isth

+05
SELECT TO_CHAR(current_timestamp, 'TZM');

SELECT TO_CHAR(current_timestamp, 'tzm');
3030

istm
SELECT TO_CHAR(current_timestamp, 'iddd');1134131
SELECT TO_CHAR(current_timestamp, 'id');165
SELECT TO_CHAR(current_timestamp, 'ssss');555564490
SELECT TO_CHAR(current_timestamp, 'hhthth');Invalid format11thth
SELECT TO_CHAR(current_timestamp, 'rmth');Invalid formatv
SELECT TO_CHAR(current_timestamp, 'FMOF');

SELECT TO_CHAR(current_timestamp, 'FMFMOF');

SELECT TO_CHAR(current_timestamp, 'FMFMFMOF');

SELECT TO_CHAR(current_timestamp, 'FMFMFMFMOF');
+5:30+5:30

FM+05:30

FM+5:30

FMFM+05:30
SELECT TO_CHAR(timestamptz'5-MAY-21 17:37:14 +05:30', 'DDTH');05th05TH
SELECT TO_CHAR(timestamptz'5-JAN-0001 17:37:14 +05:30', 'FMY,YYY');10,001
SELECT TO_CHAR(timestamptz'5-JAN-0001 17:37:14 +05:30', 'Y,YYYTH');01st0,001ST
SELECT TO_CHAR(timestamptz'5-JAN-0001 17:37:14 +05:30', 'IYYYTH');01st0001ST
SELECT TO_CHAR(timestamptz'5-JAN-0001 17:37:14 +05:30', 'YYYYTH');01st0001ST
SELECT TO_CHAR(timestamptz'5-JAN-0001 17:37:14 +05:30', 'IYYTH');01st001ST
SELECT TO_CHAR(timestamptz'5-JAN-0001 17:37:14 +05:30', 'YYYTH');01st001ST

TO_NUMBER: Converts the string to numeric according to a given format.

Note
  • PostgreSQL doesn't strictly check the format options. In contrast, EDB Postgres Advanced Server checks the format except in a few cases when the input string has '-' or ',' at the beginning. You don't have to provide the pattern for these characters in the corresponding location in the format string.
  • In PostgreSQL, you don't have to match all the digits of the input string with the patterns. It outputs the number of digits from the input string for which the valid patterns are present. EDB Postgres Advanced Server throws an error if you don't provide patterns for all the digits present in the input string.
  • PostgreSQL ignores any invalid characters present in the input or the format string. EDB Postgres Advanced Server throws an error. This kind of behavior can lead to many differences with different types of patterns.
  • The patterns 'PL', 'PR', and 'SG' aren't supported in EDB Postgres Advanced Server.

This table shows the examples for the TO_NUMBER(text, text) function:

ExamplesResult
EDB Postgres Advanced ServerPostgreSQL
SELECT TO_NUMBER('$1', '00');Invalid input syntax for type numeric: "$1"1
SELECT TO_NUMBER('$$1', '$$0');Invalid format string: "$$0"1
SELECT TO_NUMBER('1$', '0$');Invalid input syntax for type numeric: "1."1
SELECT TO_NUMBER('$1$', '$0$');Invalid format string: "$0$"1
SELECT TO_NUMBER('1$$1', '0$L0');Invalid input syntax for type numeric: "1.$1"11
SELECT TO_NUMBER('1,11', '000');11111
SELECT TO_NUMBER('1,11', '0,0');Invalid input syntax for type numeric: "111"11
SELECT TO_NUMBER('1-1', '0S0');Invalid format string: "0S0"-11
SELECT TO_NUMBER('1-1', '000');Invalid input syntax for type numeric: "1-1"11
SELECT TO_NUMBER('1.11', '000');Invalid input syntax for type numeric: "1.11"11
SELECT TO_NUMBER('1..1', '0.0');Invalid input syntax for type numeric: "1..1"1
SELECT TO_NUMBER('$11-', '$00S');Invalid input syntax for type numeric: "-$11"-11
SELECT TO_NUMBER('11', '');Invalid input syntax for type numeric: "11"Returns blank
SELECT TO_NUMBER(' ', '');Invalid input syntax for type numeric: ""Returns blank
SELECT TO_NUMBER('$12.345', 'L00.000');Invalid format string: "L00.000"12.345
SELECT TO_NUMBER('$12.345', 'L00D000');Invalid format string: "L00.000"12.345
SELECT TO_NUMBER('11-', '9MMI');Invalid format string: "9MMI"-1
SELECT TO_NUMBER('-11', 'MI99');Invalid input syntax for type numeric: "-11"-11
SELECT TO_NUMBER('000011', 'FMFM000000');Invalid format string: "FMFM000000"11
SELECT TO_NUMBER('+11', 'S99');

SELECT TO_NUMBER('11+', '99S');
Invalid input syntax for type numeric: "+11"

11
11

11
SELECT TO_NUMBER('+11', 'PL99');Invalid format string: "PL99"11
SELECT TO_NUMBER('-11', '99PR');Invalid format string: "99PR"-11
SELECT TO_NUMBER('1+1-1', '9SG9SG9');Invalid format string: "9SG9SG9"-111
SELECT TO_NUMBER('01,000', '99G999');

SELECT TO_NUMBER('01,000', 'FM99G999');
10001000

100

SUBSTR: Returns a substring of a character value.

Note
  • The index of the string starts from 1. In PostgreSQL, if zero is the start parameter to fetch only one character, the result returns blank. If you try to fetch two characters, the result shows one character since zero is specified in the start position. However, in EDB Postgres Advanced Server, if zero is the start parameter, internally it converts to 1 and starts the searching.
  • Negative values at the start parameter aren't allowed in PostgreSQL. In EDB Postgres Advanced Server, negative values cause a reverse search to occur.
  • PostgreSQL throws an error if a numeric value is passed for start and length parameter. EDB Postgres Advanced Server converts it to an integer.

This table shows the examples for SUBSTR(string, from, count), SUBSTR(string, from), SUBSTR(string, from, count)::bytea, and SUBSTR(string, from)::bytea function:

ExamplesResult
EDB Postgres Advanced ServerPostgreSQL
SELECT (SUBSTR('ABCDEFGH', 0, 1));AReturns blank
SELECT (SUBSTR('ABCD', -1, 1));

SELECT (SUBSTR('ABCD', -4, 1));
D

A
Returns blank
SELECT (SUBSTR('ABCD', 1, -1));Returns blankERROR: negative substring length not allowed
SELECT (SUBSTR('ABCD', 1.1, 1));AERROR: function substr(unknown, numeric, integer) does not exist
SELECT (SUBSTR('ABCD', 1, 1.1));AERROR: function substr(unknown, integer, numeric) does not exist
SELECT (SUBSTR('ABCD', -1));DABCD
SELECT (SUBSTR('ABCD', 1.1));ABCDERROR: function substr(unknown, numeric) does not exist
SELECT (SUBSTR('ABCDEFGH', 0, 1))::bytea;\x41\x
SELECT (SUBSTR('ABCD', 5,1))::bytea;Returns blank\x
SELECT (SUBSTR('ABCDEFGH', -1, 1))::bytea;\x48\x
SELECT (SUBSTR('ABCD', -5, 1))::bytea;Returns blank\x
SELECT (SUBSTR('ABCD', 1, -1))::bytea;Returns blankERROR: negative substring length not allowed
SELECT (SUBSTR('ABCD', 1.1, 1))::bytea;\x41ERROR: function substr(unknown, numeric, integer) does not exist
SELECT (SUBSTR('ABCD', 1, 1.1))::bytea;\x41ERROR: function substr(unknown, integer, numeric) does not exist
SELECT (SUBSTR('ABCD', 5))::bytea;Returns blank\x
SELECT (SUBSTR('ABCD', -1))::bytea;\x44\x41424344
SELECT (SUBSTR('ABCD', 1.1))::bytea;\x41424344ERROR: function substr(unknown, numeric) does not exist