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?
Replacing Single Quote with 2 Single Quotes
Moderators: chulett, rschirm, roy
Replacing Single Quote with 2 Single Quotes
Gary
"A journey of a thousand miles, begins with one step"
"A journey of a thousand miles, begins with one step"
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.
All these inner loops doest get into my head so easily during weekend.
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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)
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.