First of what is the difference between CAST (COLUMN_NAME as CHAR(XX)) and TO CHAR (COLUMN_NAME) within a view?
The issue I am having is we are in the middle of a Oracle to SQL Server conversion and data type conversions are necessary on the columns of our tables. So i made a view using the cast function like so:
CREATE OR REPLACE VIEW ARS_DMIS AS
SELECT
CAST (DMIS_TBL_ID AS CHAR(15)) "DMIS_TBL_ID",
CAST (PARENT_DMIS_CODE AS CHAR(4)) "PARENT_DMIS_CODE",
CAST (CHILD_DMIS_CODE AS CHAR(4)) "CHILD_DMIS_CODE",
CAST (SERVICE AS CHAR(15)) "SERVICE",
CAST (TYPE_FACILITY AS CHAR(10)) "TYPE_FACILITY",
CAST (UNIT_ORGANIZATION AS CHAR(60)) "UNIT_ORGANIZATION",
CAST (SITE AS CHAR(60)) "SITE",
CAST (LOCATION AS CHAR(60)) "LOCATION",
CAST (CITY AS CHAR(30)) "CITY",
CAST (STATE AS CHAR(2)) "STATE",
CAST (ZIP AS CHAR(10)) "ZIP",
CAST (DODAAC AS CHAR(10)) "DODAAC",
CAST (REGION AS CHAR(10)) "REGION",
CAST (CONUS AS CHAR(3)) "CONUS",
CAST (POC AS CHAR(30)) "POC",
CAST (POC_PHONE_NUMBER AS CHAR(20)) "POC_PHONE_NUMBER",
TO_CHAR (LAST_DATE, 'MM/DD/YYYY HH24:MI:SS') "LAST DATE",
CAST (LAST_USER AS CHAR(32)) "LAST_USER",
CAST (EMAIL AS CHAR(255)) "EMAIL",
CAST (INVENTORY_SITE AS CHAR(11)) "INVENTORY_SITE",
CAST (INVENTORY_REGION_ID AS CHAR(15)) "INVENTORY_REGION_ID",
CAST (ROWID AS CHAR(20)) "ROWID"
FROM ASSET_MGT.DMIS_TBL;
The only instance I used "TO_CHAR" was on the date because I needed to change the format of the date also and casting wouldn't allow this.
The rest of the columns I casted to the appropriate data type for the migration to SQL Server and specified the character length to match the original character length of the column from the table. But i just now found out that doing this causes white spaces to trail all the way to the specified character length.. How can i prevent this? I know I can trim it like so within the view query:
SELECT TRIM (TRAILING FROM DMIS_TBL_ID) "DMIS_TBL_ID" from ARS_DMIS_TBL
I dont want to go this route as doing this for all columns would be time consuming. Am I suing the cast function wrong? Whats the most efficient way of preventing these padding of white spaces?
CHAR(xx) is a fixed width data type. By definition, it must be space-padded out to the specified length.
VARCHAR2(xx) is a variable width data type. Based on your description, it appears that you really want to cast your columns to VARCHAR2(xx) rather than CHAR(xx).
That being said, it seems very unusual to cast all of the columns in a table to strings as part of a conversion. Surely, the destination SQL Server database is going to use date or datetime columns to store dates, it's going to use numeric data types to store numbers, etc.