Home » SQL & PL/SQL » SQL & PL/SQL » Help With Strings and Quotes (Oracle 10g)
Help With Strings and Quotes [message #280781] Wed, 14 November 2007 13:51 Go to next message
pcd_lou
Messages: 63
Registered: October 2007
Location: Florida
Member
I am trying to bring in a flat file record from an Excel spreadsheet converted to a CSV file.

My issue is that a cell seems to have the double-quote mark (") in the string. It seems to be causing parsing issues.

I tried using the Translate function to remove the ("), but that seems to cause other issues.

Does anyone have a routine that will strip out the Double-Quote (") or Single-Quote (') from a string field?

Thanks,
Lou
Re: Help With Strings and Quotes [message #280782 is a reply to message #280781] Wed, 14 November 2007 13:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

It seems to be causing parsing issues.

For who? Not Oracle.

Quote:

I tried using the Translate function to remove the ("), but that seems to cause other issues.

Which ones?

Short answer: you're doing it wrong but as you don't post any information you're on your own.

Regards
Michel
Re: Help With Strings and Quotes [message #280783 is a reply to message #280781] Wed, 14 November 2007 13:55 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
REPLACE instead of TRANSLATE?
SQL> select replace ('1234"abc', '"', '') result from dual;

RESULT
-------
1234abc
Re: Help With Strings and Quotes [message #280784 is a reply to message #280781] Wed, 14 November 2007 13:56 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
It might be easier using REPLACE; then again it might not.
Your Mileage May Vary (YMMV)
Re: Help With Strings and Quotes [message #280785 is a reply to message #280782] Wed, 14 November 2007 13:58 Go to previous messageGo to next message
pcd_lou
Messages: 63
Registered: October 2007
Location: Florida
Member
The parsing issues are the columns get out of sync for that row.

The Translate function used caused the columns to get out of sync as well.

The (InRecord) Data is:
44,ABN-Amro Holding ,Netherlands,Banking,Mr,Malcolm Coleman,Country Exec ,ABN Amro Australia Ltd,"Level 7, ABN AMRO Tower ",88 Philip Street,Sydney,NSW,2000,Australia,F2000,61 2 82595000,61 2 9259 5444,,,http://www.zoominfo.com/Search/PersonDetail.aspx?PersonID=193132651&QueryID=58ab105b-ed09-4cdf-addc-c511c1ead116,,,,,,,,,, ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,, ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,

The command I used is:
W_InRecord_Rest := Translate(W_InRecord_Rest, '"', Null);

I thought someone might have a routine that is proven to work to get the (") marks out of a string.

Let me know if you need more information.

Thanks,
Lou
Re: Help With Strings and Quotes [message #280786 is a reply to message #280781] Wed, 14 November 2007 14:05 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Ready, Fire, Aim!

Please post the control file for SQLLDR that you are using to load the data file into Oracle.

Rhetorical question -
What happens if/when you have a field that is enclosed by double quote marks, the field itself contain a comma, & then you remove the double quote marks?
Re: Help With Strings and Quotes [message #280787 is a reply to message #280783] Wed, 14 November 2007 14:06 Go to previous messageGo to next message
pcd_lou
Messages: 63
Registered: October 2007
Location: Florida
Member
I tried:

W_InRecord_Rest := Translate(W_InRecord_Rest, '"', Null);
and
W_InRecord_Rest := Translate(W_InRecord_Rest, '"', '');

and they both make my whole W_InRecord_Rest blank or Null

I just tried the Replace function.

That did it.

THANKS A BUNCH!

Lou

Re: Help With Strings and Quotes [message #280792 is a reply to message #280781] Wed, 14 November 2007 14:21 Go to previous messageGo to next message
pcd_lou
Messages: 63
Registered: October 2007
Location: Florida
Member
I found what was making the rest of the row out of sync.

The problem area was
ABN Amro Australia Ltd,"Level 7, ABN AMRO Tower ",88 Philip Street,Sydney,NSW

So my Replace took care of the (") but the parcing found the (,) within the ("") and made that a new column. So Now I need to find the first ("), remove it, find the next (,), remove it, then find the next (") and remove it. I think that will do it.

I Think that will work. Anyone find a problem with this logic?

Thanks,
Lou
Smile
Re: Help With Strings and Quotes [message #280796 is a reply to message #280781] Wed, 14 November 2007 14:27 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>Anyone find a problem with this logic?
Why are you bothering with all of this nonsense?

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
Re: Help With Strings and Quotes [message #280798 is a reply to message #280796] Wed, 14 November 2007 14:31 Go to previous message
pcd_lou
Messages: 63
Registered: October 2007
Location: Florida
Member
Yea,

I LOVE Converting Strings!

You never know what a user will put into a field.

Previous Topic: How to extract text from a long datatype
Next Topic: ORA-00904: "COPY_ID": invalid identifier
Goto Forum:
  


Current Time: Sun Dec 11 00:10:55 CST 2016

Total time taken to generate the page: 0.21097 seconds