SQL Conversion Functions and Conditional Expressions: A Practical Guide

TO_CHAR converts a date, number, or timestamp into a formatted string. You give it the value and a format mask, and it hands back text shaped exactly the way you described.

SELECT TO_CHAR(order_date, 'YYYY-MM-DD')     AS iso_date,
       TO_CHAR(order_amount, '9,999,999.99') AS formatted_amount
FROM   orders;

Order 1001's date of 2024-01-15 renders as 2024-01-15 in the first column. Its amount of 12000.456 renders as 12,000.46 in the second — the mask forces thousand separators and two decimal places.

The reason this function exists is that raw dates and raw numbers rarely look right in a report. A timestamp with timezone information is not what the reader wants. A number with six decimals is not what the invoice requires. TO_CHAR is how you decouple the storage format from the display format.

One caution worth carrying forward: once a value passes through TO_CHAR, it is a string. It no longer sorts chronologically or numerically. Sort and filter on the original column, and convert only for the SELECT output. On other databases, PostgreSQL supports TO_CHAR with the same syntax, MySQL uses DATE_FORMAT, and SQL Server uses FORMAT.

TO_NUMBER converts a string that looks like a number into an actual numeric value the database can work with. You give it the string and an optional format mask, and it returns a number.

SELECT TO_NUMBER('1234.56', '9999.99') AS converted_number
FROM   dual;

That returns the number 1234.56. Without conversion, the string '1234.56' would just be text — arithmetic on it produces nonsense, and comparisons run character-by-character instead of value-by-value.

The function is most useful when data arrives from outside sources. A CSV file loaded into a temporary table keeps everything as text by default. Any amount column needs TO_NUMBER before it can be summed. Any percentage column needs it before it can be averaged. Without it, you either get errors or, worse, silently wrong numbers.

TO_NUMBER also handles format masks that include currency symbols, thousands separators, and decimal points in non-standard positions. That matters when you are reading values a person formatted for display rather than for computation. On other databases: MySQL and PostgreSQL use CAST or CONVERT for the same purpose, and SQL Server also uses CAST or CONVERT.

TO_DATE is the reverse of TO_CHAR. It takes a string and a format mask, and returns a proper date value the database can sort, filter, and calculate with.

SELECT TO_DATE('15-08-2024', 'DD-MM-YYYY') AS converted_date
FROM   dual;

That converts the text '15-08-2024' into the actual date August 15, 2024. Once it is a date, you can add days to it, compare it against other dates, or extract the year from it.

The reason conversion matters is that string dates do not sort the way you would expect. As a string, '2024-09-01' comes after '2024-10-01' because "9" is greater than "1" at the third character. As a date, September correctly comes before October. Without conversion, every date-based comparison in a report can silently return the wrong rows.

This function comes up almost every time data is imported. External sources deliver dates as strings in whatever format the exporting system uses — DD/MM/YYYY, MM-DD-YYYY, DD-Mon-YYYY, and half a dozen others. Each needs a matching format mask. Pass the wrong one and the conversion either errors out or produces a date that is off by months, which is worse because it looks fine at a glance.

REGEXP_LIKE checks whether a string matches a regular expression pattern. It returns true or false, which makes it a natural fit inside a WHERE clause.

SELECT customer_id, customer_name
FROM   customers
WHERE  REGEXP_LIKE(customer_id, '^C[0-9]{3}$');

That pattern matches any customer id that starts with C and is followed by exactly three digits — like C001 or C007. Anything else is filtered out.

Validation is the main use. Email addresses, phone numbers, postal codes, national ID formats — anything that has a defined structure but is stored as free text is a candidate. Writing a regex for each pattern is a one-time job that saves you from writing and maintaining dozens of individual condition checks.

The function is Oracle-specific, but equivalents exist everywhere. MySQL has REGEXP, PostgreSQL has ~ and SIMILAR TO, and SQL Server uses PATINDEX for pattern matching (though without full regex support). When you are doing serious pattern validation across databases, keep the regex simple enough that the equivalents can express it.

LISTAGG aggregates values from multiple rows into a single delimited string. It is the function you reach for when you want to compress a one-to-many relationship onto one line of a report.

SELECT customer_id,
       LISTAGG(order_id, ', ') WITHIN GROUP (ORDER BY order_id) AS order_list
FROM   orders
GROUP BY customer_id;

Alice Corp, who has three orders, comes back with a single row showing 1001, 1003, 1007. Bob Traders gets 1002, 1005. Everyone else gets their own compact list.

The syntax has one unusual piece: WITHIN GROUP (ORDER BY ...). That clause controls the order of values inside the aggregated string. Without it, the order is not guaranteed and can vary between runs — which is a subtle bug in any report that assumes a consistent output.

The main risk with LISTAGG is size. If the concatenated string exceeds the maximum length the database allows — often 4000 characters for VARCHAR — the query fails. On large aggregations you sometimes have to fall back to a different approach or use LISTAGG with an ON OVERFLOW clause in newer Oracle versions.

CAST converts a value from one data type to another. The syntax is CAST(expression AS target_type), and it works the same way in every major database.

SELECT CAST('1234' AS NUMBER)          AS as_number,
       CAST(1234  AS VARCHAR(10))       AS as_text,
       CAST('2024-08-15' AS DATE)       AS as_date
FROM   dual;

Three conversions in one query. The string "1234" becomes the number 1234. The number 1234 becomes the string "1234". The text date becomes a real date.

CAST is the portable option. Because it is part of the ANSI SQL standard, a query written with CAST behaves the same on Oracle, PostgreSQL, MySQL, and SQL Server. That is a big deal when you do not know what engine will eventually run the query, or when you are sharing code across teams that use different databases.

The most common real use is converting between text and numbers, or between text and dates. Both directions show up whenever you import data from a CSV or read from a staging table that stores everything as text. The rule of thumb is: cast when you need to, but design tables so you do not have to. Storing numbers as numbers and dates as dates means you need CAST less often.

You chain them. TO_CHAR turns the date into a formatted string, and TO_NUMBER turns that string into a number.

SELECT TO_NUMBER(TO_CHAR(SYSDATE, 'YYYYMMDD')) AS numeric_date
FROM   dual;

On 2024-11-26, that returns 20241126. A plain integer that represents the date and sorts naturally as a number.

The reason this pattern exists is that numbers sort, compare, and index faster than dates in some situations. If you are generating a report key, or storing a date as an integer column in a legacy schema, or building a partition key, a numeric date representation can be more efficient.

It also shows up in calculations. Subtracting 20240101 from 20241231 does not give you the number of days between the two dates — that math only works with actual date types. But for storing and sorting, the numeric form is fine, and it avoids any ambiguity about timezone or format.

On other databases, the equivalent combines different functions. MySQL and PostgreSQL use DATE_FORMAT plus CAST. SQL Server uses CONVERT plus CAST. The principle is the same; only the function names change.

DECODE is Oracle's compact way to write an equality-based conditional expression. It compares an expression against a series of value-result pairs and returns the result tied to the first matching value.

SELECT order_id,
       order_status,
       DECODE(order_status,
              'Delivered', 'Complete',
              'Shipped',   'In Transit',
              'Pending',   'Awaiting',
                         'Unknown') AS status_label
FROM   orders;

Every order comes back with a human-readable label next to the raw status code. The trailing 'Unknown' argument is the default — what DECODE returns when none of the pairs match.

DECODE only does equality. You cannot write DECODE(amount, > 5000, ...). That syntax does not exist, and the function will error out. Anything involving ranges, comparisons, or compound conditions requires CASE, which does not have that limitation.

Where you still see DECODE is legacy Oracle code. New code should generally use CASE for two reasons. First, CASE is standard and portable. Second, CASE is easier to read when the conditions get complex — the WHEN/THEN structure scales better than a flat list of pairs.

JSON_ARRAYAGG aggregates multiple rows into a single JSON array. Instead of a comma-separated string, you get a structured value that a front-end or API can parse directly.

SELECT customer_id,
       JSON_ARRAYAGG(order_id ORDER BY order_id) AS order_ids_json
FROM   orders
GROUP BY customer_id;

Alice Corp comes back with [1001, 1003, 1007] — a proper JSON array, not a string that needs splitting.

The reason this function exists is that modern applications do not want to parse delimiters. If you are building a REST API response, the client expects JSON. If you are feeding a JavaScript front-end, an actual array is more useful than a comma-separated string. JSON_ARRAYAGG bridges the gap between relational output and the JSON shape that downstream code expects.

Support varies. Oracle, MySQL, and PostgreSQL all have JSON_ARRAYAGG in recent versions. PostgreSQL also offers JSON_AGG, which does the same thing. SQL Server does not have a direct equivalent but produces similar output via FOR JSON PATH.

EXTRACT pulls one part out of a date or time value — year, month, day, hour, minute, or second. Each call returns one number.

SELECT order_id,
       EXTRACT(YEAR  FROM order_date) AS order_year,
       EXTRACT(MONTH FROM order_date) AS order_month
FROM   orders
WHERE  order_date IS NOT NULL;

Order 1001's date of 2024-01-15 comes back as year 2024 and month 1. Order 1003's date of 2024-02-18 comes back as 2024 and 2.

The most common uses are filtering and grouping. To keep only orders from March, write WHERE EXTRACT(MONTH FROM order_date) = 3. To summarise by year, write GROUP BY EXTRACT(YEAR FROM order_date). Both are cleaner than any equivalent string manipulation, and both work regardless of the date's display format because EXTRACT operates on the underlying date value.

Some databases support additional parts — QUARTER, WEEK, DAYOFWEEK, and more. The list varies by engine, so check the docs for whatever you are working with. MySQL uses YEAR(), MONTH(), and DAY() rather than EXTRACT, and SQL Server uses DATEPART.

COLLECT is an Oracle-specific function that gathers multiple rows into a single collection type — Oracle's version of a nested table or array. The result is not a string or a JSON value but an actual collection object.

SELECT customer_id, COLLECT(order_id) AS order_ids
FROM   orders
GROUP BY customer_id;

Each customer row carries a collection of their order ids. From a plain SQL perspective, this looks similar to LISTAGG. The difference is what happens next. The collection is not text — it is a structured object that PL/SQL code can iterate over, index into, or pass to a stored procedure as a single argument.

In practice, COLLECT is used almost exclusively inside PL/SQL. It lets a procedure receive a variable-length set of values from a single query, which would otherwise require a cursor, a loop, or a temporary table. Where you see it, you are usually looking at Oracle code that does bulk processing.

Portability is limited. MySQL and PostgreSQL do not have COLLECT. PostgreSQL has ARRAY_AGG, which produces a similar structure. MySQL uses GROUP_CONCAT for the string case. SQL Server has neither — it approximates the same output with STRING_AGG or FOR XML PATH.

REPLACE searches a string for every occurrence of a substring and swaps it with a new value. Three arguments: the original string, what to find, and what to replace it with.

SELECT REPLACE(customer_name, 'Corp', 'Corporation') AS formal_name
FROM   customers;

Alice Corp becomes Alice Corporation. The change happens for every matching occurrence in the string, and for every row in the result.

The most common real use is bulk cleanup. When you inherit a dataset that uses an abbreviation, a legacy term, or a spelling variant, REPLACE rewrites the whole table in one pass. You do not need to enumerate the rows, look up each one, and issue individual updates. Wrap the column, and the function handles every row.

Two things to know. First, REPLACE is case-sensitive — 'corp' and 'Corp' are different patterns. If your data has inconsistent case, you may need multiple passes or a combination with UPPER/LOWER. Second, REPLACE matches on substring, not on whole values. REPLACE(name, 'Co', 'X') would rewrite "Alice Corp" to "Alice Xrp" — which is usually not what you meant. Choose your search pattern carefully.

CAST takes a value and returns it as a different data type. The syntax is always CAST(value AS target_type), and it works the same way in every major database.

SELECT CAST('1234' AS NUMBER) AS cast_number
FROM   dual;

That query turns the string "1234" into the number 1234. The value is now ready for arithmetic.

The three conversions you will use most often are string-to-number, number-to-string, and string-to-date. String-to-number shows up whenever you are processing imported data. Number-to-string shows up when you need to concatenate a numeric value with text. String-to-date shows up whenever a date has been stored as text and you need to compare it against real dates.

CAST is the portable choice because it is part of the ANSI SQL standard. SQL Server also offers CONVERT, which provides extra formatting options and is sometimes preferable for date-specific conversions. But CAST is what you reach for when you want code that works everywhere without modification.

XMLAGG aggregates multiple XML fragments into a single XML document. It is used when data has to be returned in XML format — mostly in legacy integrations, SOAP services, and enterprise systems that still communicate that way.

SELECT customer_id,
       XMLAGG(XMLELEMENT(NAME order, order_id)).getClobVal() AS orders_xml
FROM   orders
GROUP BY customer_id;

Each customer gets back an XML fragment containing all their order ids nested as <order> elements. The outer getClobVal() call converts the XML type to a large text value that can be returned from a query.

Where you actually see this: reporting systems that need to hand XML to another service, data exports that must conform to an XML schema, or interfaces to older enterprise software that has not moved to JSON. Modern projects rarely choose XML for new work, but old systems are still running and someone has to write the queries that feed them.

Support is Oracle and PostgreSQL native. MySQL does not have XMLAGG but has other XML functions. SQL Server produces equivalent output through FOR XML PATH, which is SQL Server's standard way of generating XML from relational data.

The purpose of TO_CHAR is to take data the database stores in one format and deliver it in another, shaped by a mask you choose.

SELECT TO_CHAR(order_date, 'DD-MM-YYYY')   AS european_date,
       TO_CHAR(order_date, 'Month YYYY')   AS month_year,
       TO_CHAR(order_amount, '$999,999.99') AS currency
FROM   orders
WHERE  order_date IS NOT NULL;

Same underlying data, three different presentations. European date format for one audience, month and year for another, currency formatting for a third. The mask is what controls the output.

The reason this function exists is that storage format and display format are different things. The database stores a date as a point in time. The reader wants to see "15 Jan 2024" or "January 2024" or just "Q1". The database stores a number as a value. The reader wants to see a currency symbol and thousand separators. TO_CHAR is where those two views meet.

Worth noting: the format mask is case-sensitive on some databases and not others. 'MON' gives an abbreviated month name in uppercase. 'Mon' gives it in mixed case. If your output looks wrong, check the mask.

TO_NUMBER converts a character string into an actual numeric value. It is the counterpart to TO_CHAR — one turns numbers into text, the other turns text into numbers.

SELECT TO_NUMBER('1234.56', '9999.99') AS converted_number
FROM   dual;

The function reads the string against the format mask and returns the number. The format mask can include currency symbols, thousand separators, and decimal points, which lets you convert values that were formatted for display.

The main use case is data imports. When you load a CSV file, everything comes in as text by default. That is fine for storing, but the moment you want to sum, average, or compare those values numerically, you need them as numbers. TO_NUMBER makes that conversion explicit. Without it, the database might do an implicit conversion — but relying on that is a good way to get bitten by locale differences and format variations.

One thing to watch for: if the string contains characters the format mask does not account for, TO_NUMBER fails with an error. That is actually helpful — it means malformed data gets caught at query time instead of silently corrupting a calculation downstream.

TO_DATE takes a string and a format mask and returns a proper date value. It is the tool that turns text into something the database can calculate with.

SELECT TO_DATE('2024-11-01', 'YYYY-MM-DD') AS converted_date
FROM   dual;

That query returns the date November 1, 2024. Once converted, the value can be sorted, filtered by range, or used in date arithmetic like order_date + 30.

The most common situation where you reach for this is data import. External systems deliver dates as text, and each system uses its own format. Your job is to write the matching format mask. 'YYYY-MM-DD' for ISO dates, 'DD/MM/YYYY' for European dates, 'MM-DD-YYYY' for U.S. dates. Getting the mask wrong either produces an error or, worse, produces a date that looks fine but is off by months.

The consequence of forgetting to convert is subtle. Comparisons on string dates run character-by-character, which gives wrong results for reasons that are not obvious at a glance. Two strings that look like sequential dates in a report might actually sort in the wrong order because of how the digits line up. Conversion is what makes date comparisons behave the way anyone would expect.

LISTAGG compresses multiple rows into a single delimited string. Use it when you want to show a one-to-many relationship on one line of a report — a customer with all their order ids, a category with all its products, a team with all its members.

SELECT customer_id,
       LISTAGG(order_id, ', ') WITHIN GROUP (ORDER BY order_id) AS orders_list
FROM   orders
GROUP BY customer_id;

Alice Corp's row shows 1001, 1003, 1007. Bob Traders shows 1002, 1005. Every customer that has at least one order appears exactly once with a comma-separated list of their order ids.

Where this is valuable is in reports and dashboards. A traditional grouped query would give Alice three rows, one per order. If the report is supposed to show a per-customer summary, three rows for Alice looks wrong. LISTAGG collapses them into one row with a readable list.

The limit to know about is size. If the aggregated string exceeds the maximum length for the target data type — often 4000 characters — the query fails with an overflow error. On large aggregations, you either need to increase the limit, use a large object type, or find a different way to present the data. Newer Oracle versions offer an ON OVERFLOW clause that lets you truncate the result rather than failing.

REGEXP_REPLACE is like REPLACE, but the pattern you search for is a regular expression rather than a literal string. That makes it far more powerful for cleaning up data where the shape of the text varies.

SELECT REGEXP_REPLACE('abc123xyz', '[0-9]', '#') AS cleaned
FROM   dual;

Every digit in the string gets replaced with a # character, producing abc###xyz. With plain REPLACE you would have to run a separate call for each possible digit. With a regex, one call handles all ten.

The real value is in cleanup work. Phone numbers stored in inconsistent formats, email addresses with stray characters, product codes with embedded whitespace — anything where the pattern is stable but the exact content varies. A regex catches all variants in one pass.

The function is available in Oracle, MySQL, and PostgreSQL with similar syntax. SQL Server does not have REGEXP_REPLACE natively, but you can achieve similar results with a combination of PATINDEX, TRANSLATE, and STRING operations — or by moving the pattern matching into an application layer. When you need complex regex replacement on SQL Server, the cleanest path is often to do the cleanup outside the database.

Both functions change the data type of a value. The difference is in portability and in how much formatting control you get.

SELECT CAST('2024' AS NUMBER) AS converted_number
FROM   dual;

CAST is the ANSI SQL standard. It works the same way in Oracle, PostgreSQL, MySQL, and SQL Server. You write CAST(value AS type) and you get back the value in the new type. Simple, consistent, portable.

CONVERT is SQL Server specific. It does the same job, but it takes an optional third argument — a style code — that controls how dates are formatted in the output. That extra parameter is why CONVERT exists separately from CAST. It handles the cases where you want a specific date format baked into the conversion itself, without a separate formatting step.

When you are writing code that needs to run on more than one database, use CAST. When you are committed to SQL Server and you need the date-style flexibility, use CONVERT. The two are not interchangeable in the sense that one is "better" — they just serve slightly different needs. For type conversion where you do not need formatting control, CAST is the right choice every time.

You put commas into the format mask. TO_CHAR reads the mask character by character and produces output that matches it.

SELECT TO_CHAR(order_amount, '9,999,999.99') AS formatted_amount
FROM   orders;

Order 1004's amount of 22000.5 comes back as 22,000.50. Order 1001's 12000.456 comes back as 12,000.46. The commas appear where the mask put them, and the decimal places match the mask exactly.

The reason this matters is that raw numbers are hard to read at scale. A value of 1234567.89 requires the reader to count digits to figure out whether it is in the millions or billions. The same number formatted as 1,234,567.89 is instantly readable. Any report meant for human consumption benefits from this kind of formatting.

The mask is flexible. '9,999' for integers with thousand separators. '$9,999.99' to prepend a currency symbol. '999.99%' to render a value as a percentage. Each character in the mask maps to a formatting instruction, and the same value rendered with different masks can look completely different. On other databases, PostgreSQL supports the same TO_CHAR syntax; MySQL uses FORMAT; SQL Server uses FORMAT with .NET-style format strings.

DECODE is Oracle's compact if-else. It compares an expression against a series of value pairs and returns the result that matches.

SELECT order_id,
       order_status,
       DECODE(order_status,
              'Delivered', 'Complete',
              'Shipped',   'In Transit',
              'Pending',   'Awaiting',
                         'Unknown') AS status_label
FROM   orders;

Every order row comes back with a label. Delivered becomes Complete, Shipped becomes In Transit, Pending becomes Awaiting. Anything else — a new status that nobody updated the code for — falls through to Unknown.

The function is useful for exactly this kind of mapping. Numeric codes to labels, abbreviations to full names, internal categories to customer-facing names. It saves you from writing a full CASE expression when all you need is a lookup.

Two limitations to know about. First, DECODE only does equality — you cannot use it with ranges or comparisons. Second, it is Oracle-specific. In any new codebase, CASE is the better choice because it supports every kind of condition and works across all databases. Where DECODE still appears is legacy Oracle code, and it is worth knowing what it does even if you would not write it from scratch.

EXTRACT pulls one component out of a date or timestamp and returns it as a number. The component is specified by a keyword like YEAR, MONTH, DAY, HOUR, MINUTE, or SECOND.

SELECT order_id,
       EXTRACT(YEAR FROM order_date) AS order_year
FROM   orders
WHERE  order_date IS NOT NULL;

Each row gets the year portion of its order date as a plain number. Filtering by year becomes WHERE EXTRACT(YEAR FROM order_date) = 2024. Grouping by month becomes GROUP BY EXTRACT(MONTH FROM order_date).

The main use case is time-based reporting. Monthly sales totals, yearly comparisons, seasonal trends — all of these need dates broken down into components before they can be grouped. EXTRACT is the cleanest way to do that because it operates on the underlying date value rather than on any formatted string.

Different databases use different names for the same idea. MySQL has YEAR(), MONTH(), and DAY() as separate functions. PostgreSQL and SQL Server both support EXTRACT. SQL Server also has DATEPART, which does the same thing with a slightly different syntax. Once you know the concept, translating between them is trivial.

Both change a value's data type. The distinction comes down to portability and to how much formatting control each function offers.

SELECT CAST('2024-11-01' AS DATE) AS cast_version,
       CONVERT(VARCHAR, GETDATE(), 120) AS convert_version
FROM   dual;

CAST is the ANSI SQL standard. It works the same way everywhere — Oracle, PostgreSQL, MySQL, SQL Server, and anything else that follows the standard. When you write CAST(value AS type), you get predictable behaviour no matter what engine runs the query.

CONVERT is SQL Server's own function. It does everything CAST does, plus it takes an optional third argument that specifies a formatting style. That style code controls how dates are rendered, which lets you produce specific date formats without a separate formatting step. For example, style 120 produces an ISO 8601 date format, style 103 produces a British date format, and so on.

The rule is straightforward. Use CAST whenever you want portable code, which is most of the time. Use CONVERT when you are writing SQL Server-only code and you specifically need the date-style control. Do not use CONVERT in a query that might need to run on PostgreSQL tomorrow — the function does not exist there, and you will have to rewrite it anyway.