Home » SQL & PL/SQL » SQL & PL/SQL » Invisible double quotes (Oracle 12c Windows10)
Invisible double quotes [message #681933] Fri, 11 September 2020 17:03 Go to next message
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 #681934 is a reply to message #681933] Fri, 11 September 2020 20:31 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member

Please read and follow the forum guidelines. post test case so we can work with same tables & data as you have.
Re: Invisible double quotes [message #681935 is a reply to message #681933] Sat, 12 September 2020 00:35 Go to previous messageGo to next message
Michel Cadot
Messages: 67363
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2985
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 #681938 is a reply to message #681933] Sat, 12 September 2020 07:48 Go to previous messageGo to next message
EdStevens
Messages: 1248
Registered: September 2013
Senior Member
What do you mean by "invisable" double-qutes? Why does that column have double-quotes? Why does it have a carraige return? Looks to me like your data is corrupted - or your data model is seriously, fatally flawed.I'd think I'd be addressing those issues.
Re: Invisible double quotes [message #681939 is a reply to message #681934] Sun, 13 September 2020 10:42 Go to previous messageGo to next message
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 #681940 is a reply to message #681939] Sun, 13 September 2020 12:36 Go to previous messageGo to next message
Michel Cadot
Messages: 67363
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

There is no double-quotes in your file, I don't know why and how you think there are in the data.

The problem you actually have comes that you have a file which is a Windows one (lines end with CRLF) and you want to load it from Linux (from your INFILE parameter) where lines end with LF alone.
You wrongly transferred the file in binary mode instead of text mode.

Re: Invisible double quotes [message #681955 is a reply to message #681940] Wed, 16 September 2020 12:43 Go to previous messageGo to next message
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 Go to previous message
Michel Cadot
Messages: 67363
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.

Previous Topic: How to retrieve data only those which are in decimals
Next Topic: understanding table partition
Goto Forum:
  


Current Time: Fri Sep 18 12:56:45 CDT 2020