Hi
I was using substr when creating a view as the source table (not my table) has all columns set to varchar2(2000). This cuases issues in SAS for our Stats team.
When I use substr in my create script, the column sizes are coming out to be 4 times larger than the substr params.
example:
Source table:
CREATE TABLE Dave_test
( Test1 varchar2(100)
,test2 varchar2(100)
,test4 varchar2(500)
,test3 number);
View script:
CREATE OR REPLACE VIEW davetest_vw
AS SELECT SUBSTR(Test1,1,10) AS test1
,SUBSTR(test2,1,20) AS test2
,SUBSTR(test4,1,200) AS test4
FROM Dave_test;
Gives me:
SQL> desc davetest_vw
Name Null? Type
----------------------------------------- -------- ----------------------------
TEST1 VARCHAR2(40)
TEST2 VARCHAR2(80)
TEST4 VARCHAR2(800)
Now, I have done my research and know the main solution is replacing substr with a CAST… CAST(TEST1 as VARCHAR2(10))
but I would like to know WHY does the substr multiple the specified size by 4?? I hate no knowing why it does this… lol
Thanks,
Dave