Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Removing a Character 2

Status
Not open for further replies.

MichaelF81

Programmer
Sep 20, 2005
178
US
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.

Code:
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
 
You didn't place brackets on the right place
Code:
LTrim(
RIGHT(qustntext, Len(qustntext)) -
 CharIndex('"', qustntext)
)???????????

Maybe you ment:
Code:
LTrim(RIGHT(qustntext, Len(qustntext) -
 CharIndex('"', qustntext)))


Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP
 
First off I made a mistake

Code:
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
 
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
 
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??????????'
:)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP
 
Uh.... glad I could help?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
not this question, but previous ones




"Adults are just obsolete children and the hell with them." - Dr. Seuss
 
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
 
Code:
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...

Code:
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
 
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
 
can't you use

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


 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top