Solved

update first character and last character

Posted on 2013-01-22
15
308 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

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 92

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 92

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Detach & Attach 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.
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

738 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