Replacing Single Quote with 2 Single Quotes

[gagan8877]

I have scenario, in whic data is being load to SQL Server db using DTS from flat files generated by DS. There is a varchar field that contains quotes and DTS bombs out saying too many columns. Here is the pseudocode:

If there is a single quote or double quote present

then if in between characters are single quotes and 1st and last are also single quotes

then add replace in-between quote with two single quotes and outside replace 1 with 3 quotes

else if in between characters are single quotes and 1st and last are not single quotes

then replace 1 quote with two single quotes and add quotes outside

else if the 1st and the last characters are single quotes and in between there are none and no double quotes

then replace quote with 3 single quotes

else if there are double-quote present and first and last are not quotes

then add single quotes outside

else if there are double-quotes present and the first and last are single quotes

then replace 1 single quote with 3 single quotes

Else
Do nothing

------------------------------

Here is the code in transformer (link out to sequential file):

IF COUNT(Lkp1Xmf2.AMEX_VAR_1_TXT, CHAR(39)) > 0 OR COUNT(Lkp1Xmf2.AMEX_VAR_1_TXT, CHAR(34)) > 0

THEN IF
(
LEFT(Lkp1Xmf2.AMEX_VAR_1_TXT, 1) = CHAR(39) AND RIGHT(Lkp1Xmf2.AMEX_VAR_1_TXT, 1) = CHAR(39) AND COUNT(Lkp1Xmf2.AMEX_VAR_1_TXT, CHAR(39)) > 2
)
OR
(
LEFT(Lkp1Xmf2.AMEX_VAR_1_TXT, 1) <> CHAR(39) AND RIGHT(Lkp1Xmf2.AMEX_VAR_1_TXT, 1) <> CHAR(39) AND COUNT(Lkp1Xmf2.AMEX_VAR_1_TXT, CHAR(39)) > 0
)
OR
(
LEFT(Lkp1Xmf2.AMEX_VAR_1_TXT, 1) = CHAR(39) AND RIGHT(Lkp1Xmf2.AMEX_VAR_1_TXT, 1) = CHAR(39) AND COUNT(Lkp1Xmf2.AMEX_VAR_1_TXT, CHAR(39)) = 2
)
OR
(
LEFT(Lkp1Xmf2.AMEX_VAR_1_TXT, 1) = CHAR(39) AND RIGHT(Lkp1Xmf2.AMEX_VAR_1_TXT, 1) = CHAR(39) AND COUNT(Lkp1Xmf2.AMEX_VAR_1_TXT, CHAR(34)) > 0
)
THEN Squote(Convert(CHAR(39), CHAR(39) : CHAR(39), Lkp1Xmf2.AMEX_VAR_1_TXT))

ELSE IF LEFT(Lkp1Xmf2.AMEX_VAR_1_TXT, 1) <> CHAR(39) AND RIGHT(Lkp1Xmf2.AMEX_VAR_1_TXT, 1) <> CHAR(39) AND COUNT(Lkp1Xmf2.AMEX_VAR_1_TXT, CHAR(34)) > 0
THEN Squote(Lkp1Xmf2.AMEX_VAR_1_TXT)

ELSE Squote(Lkp1Xmf2.AMEX_VAR_1_TXT)

ELSE Lkp1Xmf2.AMEX_VAR_1_TXT

---------------------------
sequential file Properties:

final Delimiter: end
Record Delimiter String: DOS
Null field value: \000
Quote: None
column Delimiter is pipe "|"

Input data: FIO FCEO FBEO' " (single quote and double quotes at the end)
result: ' FIO FCEO FBEO\' " '
expected result: 'FIO FCEO FBEO' ' " '

in other words when i replace single quote with 2 single quotes with convert function it gives me backslach single quote instead.

Also tried changing Record Delimiter String to Unix NewLine

The whole thing got into 1 row and it did not help.

What am I doing wrong here?

[kumar_s]

Convert function wont replace you with single qoute with double single qoute. It does character to character transformation. May be if you want, you can convert it into double qoute.
All these inner loops doest get into my head so easily during weekend.

[ray.wurlod]

As noted, Convert is one character to one character. Search the forum for a parallel equivalent of EReplace() function which can replace strings with other strings. Then you should be able to use

Code: Select all

EReplace("'", "''", InLink.TheString)