Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

update first character and last character

Posted on 2013-01-22
15
Medium Priority
?
311 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 1000 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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 1000 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

664 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