What is the best way to escape non-format characters in Oracle's to_char?
You just need double-quotes around it:
to_char(date_updated, 'YYYY-MM-DD"T"HH:mm:ss')
Oracle To_Char function How to handle if it's already a string
Rather than using REPLACE you should use the more powerful REGEXP_REPLACE function.
http://www.orafaq.com/wiki/REGEXP_REPLACE
You can then remove any non-numeric character from the string before then formatting it however you like.
In your case it would be something like:
REGEXP_REPLACE(<your field>, '[^0-9]+', '');
This replaces all non-numeric characters with null effectively removing them from the string.
See this answer too:
Oracle: Replacing non-numeric chars in a string
Why is Oracle's to_char() function adding spaces?
The format mask that you are using is fixed width and allows for a minus sign
to_char() problem decimal points and thousand separator
The B format model:
Returns blanks for the integer part of a fixed-point number when the integer part is zero (regardless of zeros in the format model).
And since you don't want a blank integer part then you probably don't want to use the B format model; instead, you want the FM format model that:
Returns a value with no leading or trailing blanks.
You can use RTRIM( TO_CHAR(var, 'FM99,999,999,990.99' ), '.' ).
For example:
SELECT var,
RTRIM( TO_CHAR(var, 'FM99,999,999,990.99' ), '.' ) AS formatted_value
FROM table_name;
Which, for the sample data:
CREATE TABLE table_name ( var ) AS
SELECT 0 FROM DUAL UNION ALL
SELECT 0.29 FROM DUAL UNION ALL
SELECT 25319.76 FROM DUAL UNION ALL
SELECT 12252.136456 FROM DUAL;
Outputs:
VAR | FORMATTED_VALUE
-----------: | :--------------
0 | 0
.29 | 0.29
25319.76 | 25,319.76
12252.136456 | 12,252.14
db<>fiddle here
Converting number to string and adding a character in Oracle
You can use:
SELECT packagenum,
despatchnum,
CASE PackageNum
WHEN 2 THEN DespatchNum || 'B'
WHEN 3 THEN DespatchNum || 'C'
WHEN 4 THEN DespatchNum || 'D'
WHEN 5 THEN DespatchNum || 'E'
ELSE TO_CHAR(DespatchNum)
END as ShipmentReference
FROM table_name;
Which, for the sample data:
CREATE TABLE table_name ( packagenum, despatchnum ) AS
SELECT LEVEL, 1234 FROM DUAL CONNECT BY LEVEL <= 6;
Outputs:
PACKAGENUM DESPATCHNUM SHIPMENTREFERENCE 1 1234 1234 2 1234 1234B 3 1234 1234C 4 1234 1234D 5 1234 1234E 6 1234 1234 Finding and removing Non-ASCII characters from an Oracle Varchar2
In a single-byte ASCII-compatible encoding (e.g. Latin-1), ASCII characters are simply bytes in the range 0 to 127. So you can use something like
[\x80-\xFF]to detect non-ASCII characters.
Related Topics
What Free SQL Formatting Tools Exist
Select Multiple Rows with the Same Value(S)
Postgresql Changing Data Directory in Ubuntu
Postgresql: Foreign Key/On Delete Cascade
How to Join on a Stored Procedure
Using Window Functions in an Update Statement
How Does SQL Query Parameterisation Work
Maintaining Referential Integrity - Good or Bad
SQL Query to Return Only 1 Record Per Group Id
How to Get Previous Business Day in a Week with That of Current Business Day Using SQL Server
Keep Only N Last Records in SQLite Database, Sorted by Date