Removing quotes from strings

bjcripe · Post by **bjcripe** » Mon Jul 12, 2004 3:44 pm

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

chulett · Post by **chulett** » Mon Jul 12, 2004 5:01 pm

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:

Code: Select all

Ereplace(YourField,"'","")

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.

ketfos · Post by **ketfos** » Mon Jul 12, 2004 5:30 pm

Hi,
Are you loading sequential file into a database or writing it to another file?

Ketfos

bjcripe · Post by **bjcripe** » Mon Jul 12, 2004 6:04 pm

Hi,

Into a database. I do not want to remove the quote marks in the data.

I want the string "Frank's and Jane's Co" to move into the database the way I receive it. The tick marks are screwing up the load. How do I ignore them on the way in????

chulett · Post by **chulett** » Mon Jul 12, 2004 7:50 pm

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:

Code: Select all

Ereplace(YourField,"'","'''")

Give that a shot. It may need to be modified based on your specific database and methodology.

vmcburney · Post by **vmcburney** » Mon Jul 12, 2004 10:57 pm

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'.

bjcripe · Post by **bjcripe** » Wed Jul 14, 2004 2:42 pm

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...

chulett · Post by **chulett** » Wed Jul 14, 2004 3:46 pm

bjcripe wrote:Can I ignore the single tick marks rather than replace them?

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.

I believe the records after the transformation and inserted into the database will exist different from the source.

No, they won't.

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?

ray.wurlod · Post by **ray.wurlod** » Thu Jul 15, 2004 4:28 am

Keep in mind that there are three quote characters available in DataStage BASIC expressions.

Code: Select all

\The title is "Alice's Restaurant."\

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 quotes

Code: Select all

Convert(\"'\,\\,TheString)

where the first argument is a two character string containing a single quote character and a double quote character.