Removing a Character 2

[MichaelF81]

In a table 'Qustn' field 'qustntext' some of the values have a quotation mark at the end. THe last character is "

How Do I remove only this.

SELECT     QustnText
FROM         dbo.Qustn
WHERE    LTrim(RIGHT(qustntext, Len(qustntext)) - CharIndex('"', qustntext))

produces

Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near ')'.

"Adults are just obsolete children and the hell with them." - Dr. Seuss

 

[bborissov]

You didn't place brackets on the right place

LTrim(
RIGHT(qustntext, Len(qustntext)) -
 CharIndex('"', qustntext)
)???????????

Maybe you ment:

LTrim(RIGHT(qustntext, Len(qustntext) -
 CharIndex('"', qustntext)))

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[MichaelF81]

First off I made a mistake

SELECT     QustnText
FROM         qustn
WHERE    LTrim(RIGHT(qustntext, Len(qustntext) -
 CharIndex('"', qustntext)))

is correct, second, I still get

Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near ')'

"Adults are just obsolete children and the hell with them." - Dr. Seuss

 

[MichaelF81]

Borislav, again, I want to thank you and George, you 2 have saved me so many times (even tried when it couldn't be done)




"Adults are just obsolete children and the hell with them." - Dr. Seuss

 

[bborissov]

OK, that is the right expression, BUT WHERE clause wants some kind of boolean expression:

SELECT     QustnText
FROM         qustn
WHERE    LTrim(RIGHT(qustntext, Len(qustntext) -
 CharIndex('"', qustntext))) = 'WHAT??????????'

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[gmmastros]

Uh.... glad I could help?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[MichaelF81]

not this question, but previous ones




"Adults are just obsolete children and the hell with them." - Dr. Seuss

 

[MichaelF81]

Helpful Member!bborissov (Programmer)
22 Jan 07 19:37
OK, that is the right expression, BUT WHERE clause wants some kind of boolean expression:
CODE
SELECT QustnText
FROM qustn
WHERE LTrim(RIGHT(qustntext, Len(qustntext) -
CharIndex('"', qustntext))) = 'WHAT??????????'
smile

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

The " is the last part of the string. So there is nothing after the " so 'is null'




"Adults are just obsolete children and the hell with them." - Dr. Seuss

 

[MichaelF81]

SELECT     QustnText
FROM         qustn
WHERE    LTrim(RIGHT(qustntext, Len(qustntext) -
 CharIndex('"', qustntext))) is null

returns a 'Null' value, now I just need an update statement to remove the " altogether...

update qustn
set qustntext = ???????
WHERE    LTrim(RIGHT(qustntext, Len(qustntext) -
 CharIndex('"', qustntext))) is null

"Adults are just obsolete children and the hell with them." - Dr. Seuss

 

[MichaelF81]

to be specific for the update statement, I just need to delete the 1 character. Is there an easy way to do this? Maybe a delete statement or something?




"Adults are just obsolete children and the hell with them." - Dr. Seuss

 

[Andel]

can't you use

update qustn
set qustntext = replace(qustntext, '"','')

 

[MichaelF81]

you know, I had tried that on a backup of the table and it did not work. I backedup the db and ran it, worked fine.

Thanks




"Adults are just obsolete children and the hell with them." - Dr. Seuss