Teradata Package for Python Function Reference on VantageCloud Lake - to_char - Teradata Package for Python - Look here for syntax, methods and examples for the functions included in the Teradata Package for Python.
Teradata® Package for Python Function Reference on VantageCloud Lake
- Deployment
- VantageCloud
- Edition
- Lake
- Product
- Teradata Package for Python
- Release Number
- 20.00.00.03
- Published
- December 2024
- ft:locale
- en-US
- ft:lastEdition
- 2024-12-19
- dita:id
- TeradataPython_FxRef_Lake_2000
- Product Category
- Teradata Vantage
- teradataml.dataframe.sql.DataFrameColumn.to_char = to_char(self, formatter=None)
- DESCRIPTION:
Converts numeric type or datetype to character type.
PARAMETERS:
formatter:
Optional Argument.
Specifies the string to format the values of a column.
Type: str
Note:
* If 'formatter' is omitted, numeric values is converted to a string exactly
long enough to hold its significant digits.
* Formatter for Numeric types:
+--------------------------------------------------------------------------------------------------+
| FORMATTER DESCRIPTION |
+--------------------------------------------------------------------------------------------------+
| , (comma) A comma in the specified position. |
| A comma cannot begin a number format. |
| A comma cannot appear to the right of a decimal |
| character or period in a number format. |
| Example: |
| +-------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------+ |
| | 1234 9,999 1,234 | |
| +-------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| . (period) A decimal point. |
| User can only specify one period in a number format. |
| Example: |
| +-------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------+ |
| | 123.46 9999.9 123.5 | |
| +-------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| $ A value with a leading dollar sign. |
| Example: |
| +-------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------+ |
| | 1234 $9999 $1234 | |
| +-------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| 0 Leading zeros. |
| Trailing zeros. |
| Example: |
| +-------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------+ |
| | 1234 09999 01234 | |
| +-------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| 9 A value with the specified number of digits with a |
| leading space if positive or with a leading minus |
| if negative. |
| Example: |
| +-------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------+ |
| | 1234 9999 1234 | |
| | 1234 999 #### | |
| +-------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| B Blank space for the integer part of a fixed point number|
| when the integer part is zero. |
| Example: |
| +-------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------+ |
| | 0.1234 B.999 Blank space| |
| +-------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| C The ISO currency symbol as specified in the ISOCurrency |
| element in the SDF file. |
| Example: |
| +-------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------+ |
| | 234 C999 USD234 | |
| +-------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| D The character that separates the integer and fractional |
| part of non-monetary values. |
| Example: |
| +-------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------+ |
| | 234.56 999D9 234.6 | |
| +-------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| EEEE A value in scientific notation. |
| Example: |
| +-------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------+ |
| | 234.56 9.9EEEE 2.3E+02 | |
| +-------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| G The character that separates groups of digits in the |
| integer part of non-monetary values. |
| +-------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------+ |
| | 123456 9G99G99 1,234,56 | |
| +-------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| L The string representing the local currency as specified |
| in the Currency element according to system settings. |
| Example: |
| +-------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------+ |
| | 234 L999 $234 | |
| +-------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| MI A trailing minus sign if the value is negative. |
| The MI format element can appear only in the last |
| position of a number format. |
| Example: |
| +-------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------+ |
| | -1234 9999MI 1234- | |
| +-------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| PR A negative value in <angle brackets>, or |
| a positive value with a leading and trailing blank. |
| The PR format element can appear only in the last |
| position of a number format. |
| Example: |
| +-------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------+ |
| | -1234 9999PR <1234> | |
| +-------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| S A negative value with a leading or trailing minus sign. |
| a positive value with a leading or trailing plus sign. |
| The S format element can appear only in the first or |
| last position of a number format. |
| Example: |
| +-------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------+ |
| | +1234 S9999 +1234 | |
| +-------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| TM (text minimum format) Returns the smallest number of |
| characters possible. This element is case insensitive. |
| TM or TM9 return the number in fixed notation unless |
| the output exceeds 64 characters. If the output exceeds |
| 64 characters, the number is returned in scientific |
| notation. |
| TME returns the number in scientific notation with the |
| smallest number of characters. |
| You cannot precede this element with an other element. |
| You can follow this element only with one 9 or one E |
| (or e), but not with any combination of these. |
| Example: |
| +-------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------+ |
| | 1234 TM 1234 | |
| +-------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| U (dual currency) The string that represents the dual |
| currency as specified in the DualCurrency element |
| according to system settings. |
| Example: |
| +-------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------+ |
| | 1234 U9999 $1234 | |
| +-------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| V A value multiplied by 10 to the n (and, if necessary, |
| rounded up), where n is the number of 9's after the V. |
| Example: |
| +-------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------+ |
| | 1234 9999V99 123400 | |
| +-------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| X The hexadecimal value of the specified number of digits.|
| If the specified number is not an integer, the function |
| will round it to an integer. |
| This element accepts only positive values or zero. |
| Negative values return an error. You can precede this |
| element only with zero (which returns leading zeros) or |
| FM. Any other elements return an error. If you do not |
| specify zero or FM, the return always has one leading |
| blank. |
| Example: |
| +-------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------+ |
| | 1234 XXXX 4D2 | |
| +-------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
* Formatter for Date types:
+--------------------------------------------------------------------------------------------------+
| FORMATTER DESCRIPTION |
+--------------------------------------------------------------------------------------------------+
| - |
| / |
| , Punctuation characters are ignored and text enclosed in |
| . quotation marks is ignored. |
| ; |
| : |
| "text" |
| Example: |
| +-------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------+ |
| | 03/09/17 MM-DD 09-17 | |
| +-------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| AD AD indicator. |
| A.D. |
| Example: |
| +-------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------+ |
| | 03/09/17 CCAD 21AD | |
| +-------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| AM Meridian indicator. |
| A.M. |
| Example: |
| +-------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------+ |
| | 03/09/17 CCAM 21AM | |
| +-------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| BC |
| B.C. BC indicator. |
| Example: |
| +-------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------+ |
| | 03/09/17 CCBC 21BC | |
| +-------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| CC Century. |
| SCC If the last 2 digits of a 4-digit year are between 01 |
| and 99 inclusive, the century is 1 greater than the |
| first 2 digits of that year. |
| If the last 2 digits of a 4-digit year are 00, the |
| century is the same as the first 2 digits of that year. |
| Example: |
| +-------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------+ |
| | 03/09/17 CCBC 21BC | |
| +-------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| D Day of week (1-7). |
| Example: |
| +-------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------+ |
| | 03/09/17 D 4 | |
| +-------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| DAY Name of day. |
| Example: |
| +-------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------+ |
| | 03/09/17 DAY WEDNESDAY | |
| +-------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| DD Day of month (1-31). |
| Example: |
| +-------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------+ |
| | 03/09/17 DD 17 | |
| +-------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| DDD Day of year (1-366). |
| Example: |
| +-------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------+ |
| | 03/09/17 DDD 260 | |
| +-------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| DL Date Long. Equivalent to the format string ‘FMDay, |
| Month FMDD, YYYY’. |
| Example: |
| +-------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------+ |
| | 03/09/17 DL Wednesday, September 17, 2003| |
| +-------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| DS Date Short. Equivalent to the format string |
| ‘FMMM/DD/YYYYFM’. |
| Example: |
| +-------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------+ |
| | 03/09/17 DS 9/17/2003 | |
| +-------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| DY abbreviated name of day. |
| Example: |
| +-------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------+ |
| | 03/09/17 DY WED | |
| +-------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| FF [1..9] Fractional seconds. |
| Use [1..9] to specify the number of fractional digits. |
| FF without any number following it prints a decimal |
| followed by digits equal to the number of fractional |
| seconds in the input data type. If the data type has no |
| fractional digits, FF prints nothing. |
| Any fractional digits beyond 6 digits are truncated. |
| Example: |
| +-------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------+ |
| | 2016-01-06 09:08:01.000000 FF 000000 | |
| +-------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| HH |
| HH12 Hour of day (1-12). |
| Example: |
| +-------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------+ |
| | 2016-01-06 09:08:01.000000 HH 09 | |
| +-------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| HH24 Hour of the day (0-23). |
| Example: |
| +-------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------+ |
| | 2016-01-06 09:08:01.000000 HH24 09 | |
| +-------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| IW Week of year (1-52 or 1-53) based on ISO model. |
| Example: |
| +-------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------+ |
| | 2016-01-06 09:08:01.000000 IW 01 | |
| +-------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| IYY |
| IY Last 3, 2, or 1 digits of ISO year. |
| I |
| Example: |
| +-------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------+ |
| | 2016-01-06 09:08:01.000000 IY 16 | |
| +-------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| IYYY 4-digit year based on the ISO standard. |
| Example: |
| +-------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------+ |
| | 2016-01-06 09:08:01.000000 IYYY 2016 | |
| +-------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| J Julian day, the number of days since January 1, 4713 BC. |
| Number specified with J must be integers. |
| Teradata uses the Gregorian calendar in calculations to |
| and from Julian Days. |
| Example: |
| +-------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------+ |
| | 2016-01-06 09:08:01.000000 J 2457394 | |
| +-------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| MI Minute (0-59). |
| Example: |
| +-------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------+ |
| | 2016-01-06 09:08:01.000000 MI 08 | |
| +-------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| MM Month (01-12). |
| Example: |
| +-------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------+ |
| | 2016-01-06 09:08:01.000000 MM 01 | |
| +-------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| MON Abbreviated name of month. |
| Example: |
| +-------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------+ |
| | 2016-01-06 09:08:01.000000 MON JAN | |
| +-------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| MONTH Name of month. |
| Example: |
| +-------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------+ |
| | 2016-01-06 09:08:01.000000 MONTH JANUARY | |
| +-------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| PM |
| P.M. Meridian indicator. |
| Example: |
| +-------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------+ |
| | 2016-01-06 09:08:01.000000 HHPM 09PM | |
| +-------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| Q Quarter of year (1, 2, 3, 4). |
| Example: |
| +-------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------+ |
| | 2016-01-06 09:08:01.000000 Q 1 | |
| +-------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| RM Roman numeral month (I - XII). |
| Example: |
| +-------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------+ |
| | 2016-01-06 09:08:01.000000 RM I | |
| +-------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| SP Spelled. Any numeric element followed by SP is spelled in|
| English words. The words are capitalized according to how|
| the element is capitalized. |
| For example: 'DDDSP' specifies all uppercase, 'DddSP' |
| specifies that the first letter is capitalized, and |
| 'dddSP' specifies all lowercase. |
| Example: |
| +-------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------+ |
| | 2016-01-06 09:08:01.000000 HHSP NINE | |
| +-------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| SS Second (0-59). |
| Example: |
| +-------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------+ |
| | 2016-01-06 09:08:01.000000 SS 03 | |
| +-------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| SSSSS Seconds past midnight (0-86399). |
| Example: |
| +-------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------+ |
| | 2016-01-06 09:08:01.000000 SSSSS 32883 | |
| +-------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| TS Time Short. Equivalent to the format string |
| 'HH:MI:SS AM'. |
| Example: |
| +-------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------+ |
| | 2016-01-06 09:08:01.000000 TS 09:08:01 AM | |
| +-------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| TZH Time zone hour. |
| Example: |
| +-------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------+ |
| | 2016-01-06 09:08:01.000000 TZH +00 | |
| +-------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| TZM Time zone minute. |
| Example: |
| +-------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------+ |
| | 2016-01-06 09:08:01.000000 TZM 00 | |
| +-------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| TZR Time zone region. Equivalent to the format string |
| 'TZH:TZM'. |
| Example: |
| +-------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------+ |
| | 2016-01-06 09:08:01.000000 TZR +00:00 | |
| +-------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| WW Week of year (1-53) where week 1 starts on the first day |
| of the year and continues to the 7th day of the year. |
| Example: |
| +-------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------+ |
| | 2016-01-06 09:08:01.000000 WW 01 | |
| +-------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| W Week of month (1-5) where week 1 starts on the first day |
| of the month and ends on the seventh. |
| Example: |
| +-------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------+ |
| | 2016-01-06 09:08:01.000000 W 1 | |
| +-------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| X Local radix character. |
| Example: |
| +-------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------+ |
| | 2016-01-06 09:08:01.000000 MMXYY 01.16 | |
| +-------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| Y,YYY Year with comma in this position. |
| Example: |
| +-------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------+ |
| | 2016-01-06 09:08:01.000000 Y,YYY 2,016 | |
| +-------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| YEAR Year, spelled out. S prefixes BC dates with a minus sign.|
| SYEAR |
| Example: |
| +-------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------+ |
| | 2016-01-06 09:08:01.000000 YEAR TWENTY SIXTEEN| |
| +-------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| YYYY |
| SYYYY 4-digit year. S prefixes BC dates with a minus sign. |
| Example: |
| +-------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------+ |
| | 2016-01-06 09:08:01.000000 YYYY 2016 | |
| +-------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| YYY Last 3, 2, or 1 digit of year. |
| YY If the current year and the specified year are both in |
| Y the range of 0-49, the date is in the current century. |
| Example: |
| +-------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------+ |
| | 2016-01-06 09:08:01.000000 YY 16 | |
| +-------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
RAISES:
TypeError, ValueError, TeradataMlException
Returns:
ColumnExpression
EXAMPLES:
# Load the data to run the example.
>>> load_example_data("uaf", "stock_data")
# Create a DataFrame on 'stock_data' table.
>>> df = DataFrame("stock_data")
>>> df
seq_no timevalue magnitude
data_set_id
556 3 19/01/16 61.080
556 5 19/01/30 63.810
556 6 19/02/06 63.354
556 7 19/02/13 63.871
556 9 19/02/27 61.490
556 10 19/03/06 61.524
556 8 19/02/20 61.886
556 4 19/01/23 63.900
556 2 19/01/09 61.617
556 1 19/01/02 60.900
>>> df.tdtypes
column type
data_set_id INTEGER()
seq_no INTEGER()
timevalue DATE()
magnitude FLOAT()
# Example 1: Convert 'seq_no' column to character type.
>>> res = df.assign(seq_no = df.seq_no.to_char())
>>> res
seq_no timevalue magnitude
data_set_id
556 3 19/01/16 61.080
556 5 19/01/30 63.810
556 6 19/02/06 63.354
556 7 19/02/13 63.871
556 9 19/02/27 61.490
556 10 19/03/06 61.524
556 8 19/02/20 61.886
556 4 19/01/23 63.900
556 2 19/01/09 61.617
556 1 19/01/02 60.900
>>> res.tdtypes
columnn type
data_set_id INTEGER()
seq_no VARCHAR()
timevalue DATE()
magnitude FLOAT()
# Example 2: Convert "magnitude" column to character type in '$99.9' format.
>>> res = df.assign(char_column = df.magnitude.to_char('$99.9'))
>>> res
seq_no timevalue magnitude char_column
data_set_id
556 3 19/01/16 61.080 $61.1
556 5 19/01/30 63.810 $63.8
556 6 19/02/06 63.354 $63.4
556 7 19/02/13 63.871 $63.9
556 9 19/02/27 61.490 $61.5
556 10 19/03/06 61.524 $61.5
556 8 19/02/20 61.886 $61.9
556 4 19/01/23 63.900 $63.9
556 2 19/01/09 61.617 $61.6
556 1 19/01/02 60.900 $60.9
# Example 3: Convert "timevalue" column to character type in 'YYYY-DAY-MONTH' format
>>> res = df.assign(timevalue = df.timevalue.to_char('YYYY-DAY-MONTH'))
>>> res
seq_no timevalue magnitude
data_set_id
556 3 2019-WEDNESDAY-JANUARY 61.080
556 5 2019-WEDNESDAY-JANUARY 63.810
556 6 2019-WEDNESDAY-FEBRUARY 63.354
556 7 2019-WEDNESDAY-FEBRUARY 63.871
556 9 2019-WEDNESDAY-FEBRUARY 61.490
556 10 2019-WEDNESDAY-MARCH 61.524
556 8 2019-WEDNESDAY-FEBRUARY 61.886
556 4 2019-WEDNESDAY-JANUARY 63.900
556 2 2019-WEDNESDAY-JANUARY 61.617
556 1 2019-WEDNESDAY-JANUARY 60.900