Solved

update first character and last character

Posted on 2013-01-22
15
309 Views
Last Modified: 2013-02-24
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?
0
Comment
Question by:fwstealer
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 3
  • +1
15 Comments
 
LVL 40

Assisted Solution

by:lcohan
lcohan earned 250 total points
ID: 38806361
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 38806367
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
 
LVL 40

Expert Comment

by:lcohan
ID: 38806371
if you want to replace leading/trailing spaces then use a UPDATE with SUBSTRING or the above mentioned functions.
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 

Author Comment

by:fwstealer
ID: 38806382
jimpen - won't that replace all double quotes?
0
 
LVL 40

Expert Comment

by:lcohan
ID: 38806409
that will replace ALL Char(34) characters with '' or empty string so caution with that...
0
 

Author Comment

by:fwstealer
ID: 38806427
yep did that with a temp table first so I need to try the left and right as you suggested lcohan
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 38806438
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
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 38806460
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
 
LVL 38

Expert Comment

by:Jim P.
ID: 38806506
@matthewspatrick,

Show off! :-p
0
 

Author Comment

by:fwstealer
ID: 38806745
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
 

Author Comment

by:fwstealer
ID: 38806817
oh how about:  

update table
set column = case
WHEN column LIKE '%""' THEN LEFT(column, LEN(column) - 1)
ELSE column END
0
 

Author Comment

by:fwstealer
ID: 38806825
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
 
LVL 38

Expert Comment

by:Jim P.
ID: 38806866
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
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 250 total points
ID: 38806940
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
 
LVL 38

Expert Comment

by:Jim P.
ID: 38807293
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

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

622 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question