Solved

update first character and last character

Posted on 2013-01-22
15
306 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
  • 5
  • 5
  • 3
  • +1
15 Comments
 
LVL 39

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 39

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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

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

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Gettg error - Please help Msg 252, Level 16, State 1, Line 1 3 30
SQL Help 27 57
Text file into sql server 5 32
Re-appearing SQL Server Agent jobs 7 29
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

856 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