How to replace a text

Question

How to replace a text

Nuts

SSCrazy

Points: 2547
More actions
August 21, 2008 at 9:41 pm

#196620

Hi guys
I have got a column in my table which is enclosed in the double quotes. I need to remove these quotes from the entire column. How do I do that through a query??
Sample
column_A
"1233"
"4445"
"876"
.
.
.
Thanks Guys
Cheers

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Anam Verma SSCertifiable Points: 5365 More actions · Answer 1

Nuts (8/21/2008)
Hi guys
I have got a column in my table which is enclosed in the double quotes. I need to remove these quotes from the entire column. How do I do that through a query??
Sample
column_A
"1233"
"4445"
"876"
.
.
.
Thanks Guys
Cheers

you can use

select replace(column_A, """"",'')

from tableA

Nuts SSCrazy Points: 2547 More actions · Answer 2

It doesnt work with this query!

The column remains unchanged (enclosed with the double quotes)

rosh-396756 SSCrazy Points: 2338 More actions · Answer 3

rosh-396756

SSCrazy

Points: 2338

August 21, 2008 at 10:52 pm

#861656

how about this

select replace(column A, '"','')

Nuts SSCrazy Points: 2547 More actions · Answer 4

Thanks

This query works but how do I save these changes I mean when I run this query, it just displays the result into the window but how do I actually update the table??

Thanks

rosh-396756 SSCrazy Points: 2338 More actions · Answer 5

update table

set column_A = replace(column_A, '"','')

Nuts SSCrazy Points: 2547 More actions · Answer 6

Nuts

SSCrazy

Points: 2547

August 24, 2008 at 3:38 pm

#862329

Thanks a lot

It works well

Cheers:D

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 7

I guess a better question would be, how did the double quotes get there in the first place? Did you import some text-qualified CSV or something? If so, what did you use? And, if so, can you attach one of the files so we can show you how to import the data without including the double quotes? 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Nuts SSCrazy Points: 2547 More actions · Answer 8

Hi

I had to import a txt file into sql server . I have attached the sample file for your reference.

It would be really good to know how to import it without including the double quotes.

Cheers

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 9

Nuts (8/24/2008)
Hi
I had to import a txt file into sql server . I have attached the sample file for your reference.
It would be really good to know how to import it without including the double quotes.
Cheers

Oh my... they certainly didn't do you any favors. The header uses different delimiters than the body of the file which makes it just about impossible to do in a straight forward manner with BCP or Bulk Insert. Lemme see what I can churn up...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Fishbarnriots SSCrazy Points: 2568 More actions · Answer 10

The file looks like a complete dogs dinner, seems like a mixture of comma, tab and pipe delimited with an apparent header row in a different format pasted in for good measure. Personally I would bounce it back to whoever provided it to you and ask them nicely, but firmly to provide the file again, but in a recognisable format and with a file layout (Fixed width would be nice).

To strip out the double quotes you could try using replace([Your column name], char(34), '') in your update statement, but I think the data would still contain allsorts of garbage.

Anam Verma SSCertifiable Points: 5365 More actions · Answer 11

import the txt file into excel through import data option. select the column delimeter as tab and save it is as excel worksheet. now import the data into sql server from excel sheet. while importing the data remember to change the column length while importing the data.

Note: if you are using excel 97-2003 you have to split the file into two excel files as it doesn't support rows more than 65336. Import the data from two excel files and then merge it.

My honest advice, ask the generator of file to make column headings more readable without any special characters. No doubt sql server will accept it but you'll be in more troubles while processing the data later on in sps or udfs. you could ask for mapping document with more info about the columns along with data file.

Perry Whittle SSC Guru Points: 234013 More actions · Answer 12

Fishbarnriots (8/26/2008)
The file looks like a complete dogs dinner

my my, as tasty as that 😀 :Whistling:

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

"Ya can't make an omelette without breaking just a few eggs" 😉

Nuts SSCrazy Points: 2547 More actions · Answer 13

Thanks for your help guys!

Will definately follow your advise

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 14

Sorry... new job has kept me busy... I think I may have a solution soon. Yep, I know you already have one using REPLACE on the quotes... there's just gotta be a better way.

I do agree with FishBarnRiots... to an extreme... get your bat, slingshot, and trebuchet and convice the providers of this horribly formatted file to fix it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)