Invisible double quotes [message #681933] |
Fri, 11 September 2020 17:03 |
pstanand
Messages: 133 Registered: February 2005 Location: Chennai,India
|
Senior Member |
|
|
Hi I have a table where I have productsku_id as varchar2 of data type. It contains data as number but within double quotes.
I want to insert this value into another table where it is declared as number.
But the to_number function fails as this invisible double quotes prevents it from converting.
Data looks like.
"262194
"
I tried repalce(productsku_id,'"','') but it is not removing the quotes.
Could you please provide a solution to this?
Thanks.
|
|
|
|
Re: Invisible double quotes [message #681935 is a reply to message #681933] |
Sat, 12 September 2020 00:35 |
|
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
From your topic "Carriage Return Finding":
Michel Cadot wrote on Thu, 11 April 2019 11:05
Put an actual test case of what you have and the actual result you want from it.
Give the rules to go from the test case to the result.
Michel Cadot wrote on Thu, 11 April 2019 11:31
Isn't my sentence clear?
...
I want you provide a test case what you have NOW.
And I want you provide the rules that is how do you want to...
Michel Cadot wrote on Thu, 11 April 2019 12:08
This is not the answer of my question which requires a test case.
...
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.
Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.
With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
|
|
|
Re: Invisible double quotes [message #681937 is a reply to message #681933] |
Sat, 12 September 2020 05:57 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
I think you are making wrong assumption when that insert fails:
SQL> with t as (select '"262194
2 "' productsku_id from dual
3 )
4 select to_number(replace(productsku_id,'"'))
5 from t
6 /
select to_number(replace(productsku_id,'"'))
*
ERROR at line 4:
ORA-01722: invalid number
SQL>
It failed because string is double-quote, number, new line, double-quote. You replaced double-quote but new-line is still preventing conversion to number. If so, use:
with t as (select '"262194
"' productsku_id from dual
)
select to_number(rtrim(ltrim(productsku_id,'"'),'"' || chr(10)))
from t
/
TO_NUMBER(RTRIM(LTRIM(PRODUCTSKU_ID,'"'),'"'||CHR(10)))
-------------------------------------------------------
262194
SQL>
SY.
|
|
|
|
Re: Invisible double quotes [message #681939 is a reply to message #681934] |
Sun, 13 September 2020 10:42 |
pstanand
Messages: 133 Registered: February 2005 Location: Chennai,India
|
Senior Member |
|
|
Hi Regret the inconvenience.
Here is the code for my issue. The table script and ctl file script. Also attached the test file. I tried to insert the data from the txt file to the table using sql loader with control file.
Please let me know your suggestions.
Thanks in advance.
CREATE TABLE Productsku
(
ITEM_NO VARCHAR2(50 BYTE),
ITEM_NO_STRIPPED VARCHAR2(50 BYTE),
ITEM_DESC VARCHAR2(500 BYTE),
VEN_CAT_NO VARCHAR2(50 BYTE),
MFG_NO VARCHAR2(50 BYTE),
PRODUCTSKU_ID VARCHAR2(50 BYTE)
);
OPTIONS(SKIP=1)
LOAD DATA
INFILE '/home/Test_Data_File.txt'
BADFILE 'Test_Data_Bad.bad'
DISCARDFILE 'Test_Data_Discard.dsc'
APPEND
INTO TABLE Productsku
FIELDS
TERMINATED BY '|'
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
ITEM_NO "TRIM(:ITEM_NO)" ,
ITEM_NO_STRIPPED "TRIM(:ITEM_NO_STRIPPED)" ,
ITEM_DESC NULLIF ITEM_DESC=BLANKS,
VEN_CAT_NO "TRIM(:VEN_CAT_NO)",
MFG_NO "TRIM(:MFG_NO)",
PRODUCTSKU_ID "TRIM(:PRODUCTSKU_ID)"
)
|
|
|
|
Re: Invisible double quotes [message #681955 is a reply to message #681940] |
Wed, 16 September 2020 12:43 |
pstanand
Messages: 133 Registered: February 2005 Location: Chennai,India
|
Senior Member |
|
|
I used this TRIM(REPLACE(REPLACE(:PRODUCTSKU_ID,CHR(13),),CHR(10),)) in my control file and loaded the data using sql loader, then I was able to get the records without double quotes.
Even I couldn't find how the quotes is coming even though it was not there in the file physically. But the above one is worked for me.
Thanks all for your suggestions.
|
|
|
Re: Invisible double quotes [message #681958 is a reply to message #681955] |
Wed, 16 September 2020 14:08 |
|
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Quote:Even I couldn't find how the quotes is coming even though it was not there in the file physically.
It is coming from the client tool you used to query the table.
|
|
|