August 5, 2014 at 3:11 am
hi
im using the following code to remove a comma
REPLACE([Counter Party Ref],", "," ")
the value in the column comes in like this
EDQE40000222004,0S0
any reason why my replace wont work or is there a way of removing it all together
August 5, 2014 at 3:15 am
Try the expression without a space after the comma.
REPLACE([Counter Party Ref],",","")
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 5, 2014 at 3:16 am
ronan.healy (8/5/2014)
hiim using the following code to remove a comma
REPLACE([Counter Party Ref],", "," ")
the value in the column comes in like this
EDQE40000222004,0S0
any reason why my replace wont work or is there a way of removing it all together
You have spurious spaces in your expression. Try this:
REPLACE([Counter Party Ref],",","")
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 5, 2014 at 3:21 am
Phil Parkin (8/5/2014)
ronan.healy (8/5/2014)
hiim using the following code to remove a comma
REPLACE([Counter Party Ref],", "," ")
the value in the column comes in like this
EDQE40000222004,0S0
any reason why my replace wont work or is there a way of removing it all together
You have spurious spaces in your expression. Try this:
REPLACE([Counter Party Ref],",","")
Too slow 😉
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 5, 2014 at 3:22 am
its seems it has been slpit out before it reaches my replace.
I have a flat file then the replace. but after I read the falt file its like this
Counter Party Ref Custodian
EDQE40000222004
but I need it like
Counter Party Ref Custodian
EDQE400002220040S0 bbbbb
August 5, 2014 at 3:27 am
ronan.healy (8/5/2014)
its seems it has been slpit out before it reaches my replace.I have a flat file then the replace. but after I read the falt file its like this
Counter Party Ref Custodian
EDQE40000222004
but I need it like
Counter Party Ref Custodian
EDQE400002220040S0 bbbbb
So if I guess this correctly, you mean you have a comma in your data in your comma-seperated text file, right?
And you don't use text delimiters, so now your columns go all bonkers. Correct?
The solution is simple: use either text delimiters, or use a decent column delimiter.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 5, 2014 at 3:39 am
its a csv file and in the csv file is all in the 1 column so should be split out correctly
August 5, 2014 at 3:46 am
ronan.healy (8/5/2014)
its a csv file and in the csv file is all in the 1 column so should be split out correctly
Are you opening it in Excel?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 5, 2014 at 3:47 am
ronan.healy (8/5/2014)
its a csv file and in the csv file is all in the 1 column so should be split out correctly
If it's a single column, how are you doing the splitting?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 5, 2014 at 3:49 am
its not a single column there is 20 columns in the file. I can see why its causing the error. on the column delimiter its set to comma(,)
if I try anything else it doesn't bring back any data. is there any way around this
August 5, 2014 at 3:54 am
ronan.healy (8/5/2014)
its not a single column there is 20 columns in the file. I can see why its causing the error. on the column delimiter its set to comma(,)if I try anything else it doesn't bring back any data. is there any way around this
Use a different column delimiter.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 5, 2014 at 4:02 am
iv tried them all they don't seem to work. I have colons and semi colons in some fields so cant use that. when I try tab or vertical get an error message.
if I use {cr} or {lf} all data and columns headers goers into 1 row
August 5, 2014 at 4:19 am
ronan.healy (8/5/2014)
iv tried them all they don't seem to work. I have colons and semi colons in some fields so cant use that. when I try tab or vertical get an error message.if I use {cr} or {lf} all data and columns headers goers into 1 row
What about pipe (|) and tilde (~)?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 5, 2014 at 4:21 am
ya use them to no difference. its grand the client will just have to use tab delimited text files going forward if they are going to have bad data all over the place
August 5, 2014 at 4:35 am
ronan.healy (8/5/2014)
ya use them to no difference. its grand the client will just have to use tab delimited text files going forward if they are going to have bad data all over the place
No difference? I think that you must have misconfigured something, because it should definitely make a difference. And surely the text is more likely to contain a [tab] than a |?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply