February 2, 2024 at 6:32 am
trying to replace double quotes in SSIS derived column using replace:
My input file has header and I need to replace EXTRA double quotes with null in column F.
Input file:
#A|B|C|D|E|F|G
"2023-02-20"|"ABC"|"123"||"4 - Stop Doing"|"UGG"|"1234"
"2023-12-20"|"ABC"|"456"||"3 - Change Offers"|"1234-28" "|"1234"
"2021-12-20"|"ABC"|"714"||"3 - Change Offers"|"0076-26" "|"1234"
"2020-12-20"|"ABC"|"717"||"3 - Change Offers"|"12543-26" "|"1234"
"2021-12-20"|"ABC"|"713"||"3 - Change Offers"|"353-26" "|"1234"
"2023-12-20"|"ABC"|"246"||"3 - Change Offers"|"1-26" "|"1234"
Desired Output:
"2023-02-20"|"ABC"|"123"||"4 - Stop Doing"|"UGG"|"1234"
"2023-12-20"|"ABC"|"456"||"3 - Change Offers"|"1234-28"|"1234"
"2021-12-20"|"ABC"|"714"||"3 - Change Offers"|"0076-26"|"1234"
"2020-12-20"|"ABC"|"717"||"3 - Change Offers"|"12543-26"|"1234"
"2021-12-20"|"ABC"|"713"||"3 - Change Offers"|"353-26"|"1234"
"2023-12-20"|"ABC"|"246"||"3 - Change Offers"|"1-26"|"1234"
I have used:
REPLACE(trim(F),"\"\"","\"\) it did not work for me
REPLACE(F,"\" ","") it did not work for me
February 2, 2024 at 10:03 am
Please try this:
REPLACE( F , "\"", "" )
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
February 2, 2024 at 10:06 am
Note also that there is a difference between an empty string ("") and NULL.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
February 2, 2024 at 2:33 pm
If you're using >= 2017, the TRIM function in T-SQL will handle the removal of double quotes and spaces and whatever other leading/trailing characters you may have to work with. It's much better than just using REPLACE because you might not want to remove "embedded" double quotes. TRIM will only remove them if they qualify as leading or trailing characters and will leave "embedded" double quotes alone.
[EDIT] And, to be sure, I'm not a DAX/SSIS type of person and don't know if TRIM works the same way is SSIS as it would for T-SQL, in this case. According to the REPLACE example that Phil posted, I'm thinking not.
[EDIT2] I also didn't look at the last two lines of the original post. The REPLACE example there has an extra space in it that I don't believe should be there and Phil's example doesn't include that extra space and will likely work just fine.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 2, 2024 at 2:58 pm
No, it did not work and that was the reason I took time to post it here. I have tried many options including substring.
February 2, 2024 at 2:59 pm
The extra space was intentional to replace double quotes with space to avoid trim. Also I had put the space manually in the file to try this out.
February 2, 2024 at 3:35 pm
OK, just to be clear, are you saying that you want to change this
"1234-28" "
to this
"1234-28"
?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
February 2, 2024 at 4:03 pm
This expression will do that:
right(F,3) == "\" \""? replace(F,"\" \"","\""): F
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
February 3, 2024 at 4:26 pm
No, it did not work and that was the reason I took time to post it here. I have tried many options including substring.
Yeah... sorry... admittedly, I only took a quick look but what you were looking for wasn't obvious to me. I see it now.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply