update first character and last character

how do i update the first and last character in a column where the value is " and I want it to be removed?

example of data:
"FLAP, R/H IB"
"size of 1/2" over"

need it as:
FLAP, R/H IB
size of 1/2" over

is it like an update?
update table set column = REPLACE(column , '"' , '') where column like '%"'
and column like '"%'

wont that leave an empty space so I'd need to trim it right?
fwstealerAsked:
Who is Participating?
 
Patrick MatthewsCommented:
Might be simpler to just do two updates:

UPDATE table
SET column = SUBSTRING(column, 2, LEN(column))
WHERE column LIKE '"%'

UPDATE table
SET column = SUBSTRING(column, 1, LEN(column) - 1)
WHERE column LIKE '%"'

Open in new window

0
 
lcohanDatabase AnalystCommented:
0
 
Jim P.Commented:
In the ball park.

Update MyTable
Set MyColumn = Replace(MyColumn, Char(34), '')
Where MyColumn Like '%' + Char(34) + '%'

Open in new window


The Char(34) is the ASCII for a double quote mark.
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
lcohanDatabase AnalystCommented:
if you want to replace leading/trailing spaces then use a UPDATE with SUBSTRING or the above mentioned functions.
0
 
fwstealerAuthor Commented:
jimpen - won't that replace all double quotes?
0
 
lcohanDatabase AnalystCommented:
that will replace ALL Char(34) characters with '' or empty string so caution with that...
0
 
fwstealerAuthor Commented:
yep did that with a temp table first so I need to try the left and right as you suggested lcohan
0
 
Jim P.Commented:
It would. Didn't notice the 1/2 inch quote.

The best way then would be to run two update queries along the lines of lcohan's suggestion. It would probably be something along the lines of:

Update MyTable
Set MyColumn = RTrim(Substring(MyColumn, 2, 4000))
Where MyColumn Like Char(34) + '%'

Open in new window

                                           
Update MyTable
Set MyColumn = Left(MyColumn, Len(MyColumn)-1)
Where MyColumn Like  '%' + Char(34) 

Open in new window


It could probably be done in one pass, but that can get messy.
0
 
Patrick MatthewsCommented:
UPDATE table
SET column = CASE
    WHEN column LIKE '"%"' THEN SUBSTRING(column, 2, LEN(column) - 2)
    WHEN column LIKE '"%' THEN SUBSTRING(column, 2, LEN(column))
    WHEN column LIKE '%"' THEN LEFT(column, LEN(column) - 1)
    ELSE column END
0
 
Jim P.Commented:
@matthewspatrick,

Show off! :-p
0
 
fwstealerAuthor Commented:
matthewspatrick - the first " is removed; however, the last " is now different such as:

current example:  "1/8"" HEX NUT CLECO, 0-1/2"""

after running your update:  1/8"" HEX NUT CLECO, 0-1/2""

so the data should be:  1/8" HEX NUT CLECO, 0-1/2"
0
 
fwstealerAuthor Commented:
oh how about:  

update table
set column = case
WHEN column LIKE '%""' THEN LEFT(column, LEN(column) - 1)
ELSE column END
0
 
fwstealerAuthor Commented:
well that took care of those at the end of the string just not:

1/8"" HEX NUT CLECO, 0-1/2"

dang -- need to find where there is a ""
0
 
Jim P.Commented:
That is the right results. You'll have to do an additional Update query in those cases

Update MyTable
Set MyColumn = Replace(MyColumn, Char(34)  + Char(34),  Char(34))
Where MyColumn Like '%'  + Char(34) + Char(34) + '%'

Open in new window


The double quotes are doubled to show that it isn't the end of the string, but a literal double quote. That is coming from your source data.
0
 
Jim P.Commented:
Either the single one with the case statement or the two like mine and yours, he'll have to do another one to handle the double quotes in the text like 1/8"" or 1/2"".
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.