Removing quotes from strings
Moderators: chulett, rschirm, roy
Removing quotes from strings
Hello,
I was loading sequencial file the is pipe delimited. On a record the job failed because a string being loaded looked like this -- "Frank's and Jane's Company". The problem is the ticks on Frank and Jane. Does anyone know how to ignore these in reading in strings?
Thanks,
Brian
I was loading sequencial file the is pipe delimited. On a record the job failed because a string being loaded looked like this -- "Frank's and Jane's Company". The problem is the ticks on Frank and Jane. Does anyone know how to ignore these in reading in strings?
Thanks,
Brian
Clarify something - you had a problem actually reading this in using the Sequential stage or a problem loading it into your target table?
The single quotes should just be treated like data when reading something like that in where you've got pipes as delimiters and double-quotes for your text strings, so I'm guessing the issue isn't there.
More than likely the issue is in the target database... is it Oracle? It wants all string values enclosed in single quotes in things like insert statements, so single quotes in the data can be hazardous to your jobs health.
A couple of choices: remove them or replace them with something it will like. In any case, check the online help for the Ereplace function, which is one way to correct this problem. You basically pass it two parameters, the string to look for and the string to replace it with. For example:
This would replace all occurances of a single quote with an empty string, in essence removing them. Depending on your database and what they would need to look like to preserve them in the data field, you could also use this same syntax to replace them appropriately.
The single quotes should just be treated like data when reading something like that in where you've got pipes as delimiters and double-quotes for your text strings, so I'm guessing the issue isn't there.
More than likely the issue is in the target database... is it Oracle? It wants all string values enclosed in single quotes in things like insert statements, so single quotes in the data can be hazardous to your jobs health.
A couple of choices: remove them or replace them with something it will like. In any case, check the online help for the Ereplace function, which is one way to correct this problem. You basically pass it two parameters, the string to look for and the string to replace it with. For example:
Code: Select all
Ereplace(YourField,"'","")
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
What database and exactly how are you 'loading' it? The answer to your question could vary based on that information.
Something to try: use the Ereplace function mentioned earlier to replace all single quotes with three single quotes - in essence quoting the quotes so they don't upset your load:
Give that a shot. It may need to be modified based on your specific database and methodology.
Something to try: use the Ereplace function mentioned earlier to replace all single quotes with three single quotes - in essence quoting the quotes so they don't upset your load:
Code: Select all
Ereplace(YourField,"'","'''")
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
It's going to need either double or triple quoting using that EREPLACE command.
On SQL Server you need to replace a single ' with the double single '':
select 'frank''s and jane''s Co' (is valid)
---------------------
frank's and jane's Co
(1 row(s) affected)
But:
select 'frank'''s and jane'''s Co' (invalid)
select 'frank's and jane's Co' (invalid)
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'and'.
On SQL Server you need to replace a single ' with the double single '':
select 'frank''s and jane''s Co' (is valid)
---------------------
frank's and jane's Co
(1 row(s) affected)
But:
select 'frank'''s and jane'''s Co' (invalid)
select 'frank's and jane's Co' (invalid)
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'and'.
Certus Solutions
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
The database is informix. I am loading from seq. flat file into odbc using a server job. If I use the Ereplace I think I will be losing data on the way in -- for example
Data coming in pipe delimited--
rec1|"Frank's and Jane's Co"|12/3/72
rec2|"Bob's Resturant"|12/3/72
I believe the records after the transformation and inserted into the database will exist different from the source.
Can I ignore the single tick marks rather than replace them?
Thank you...
Data coming in pipe delimited--
rec1|"Frank's and Jane's Co"|12/3/72
rec2|"Bob's Resturant"|12/3/72
I believe the records after the transformation and inserted into the database will exist different from the source.
Can I ignore the single tick marks rather than replace them?
Thank you...
No, not per se. What we're trying to get you to is a format that Informix will like for loading. You will end up replacing the single tick with two or three ticks, whichever allows the data to be loaded without error. What you'll end up with in the database will just be the single tick.bjcripe wrote:Can I ignore the single tick marks rather than replace them?
No, they won't.I believe the records after the transformation and inserted into the database will exist different from the source.
So, in a sense, your answer is also yes. By quoting the single quote mark it will be treated like data and not a delimiter. But in order to ignore it, you'll have to replace it. Make any sense?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Keep in mind that there are three quote characters available in DataStage BASIC expressions. is a valid string constant.
If you're only converting on a character-by-character basis, Convert() is a more efficient function to use than Ereplace(). To remove both single and double quoteswhere the first argument is a two character string containing a single quote character and a double quote character.
Code: Select all
\The title is "Alice's Restaurant."\
If you're only converting on a character-by-character basis, Convert() is a more efficient function to use than Ereplace(). To remove both single and double quotes
Code: Select all
Convert(\"'\,\\,TheString)
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.