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?
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
if you want to replace leading/trailing spaces then use a UPDATE with SUBSTRING or the above mentioned functions.
ASKER
jimpen - won't that replace all double quotes?
that will replace ALL Char(34) characters with '' or empty string so caution with that...
ASKER
yep did that with a temp table first so I need to try the left and right as you suggested lcohan
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:
It could probably be done in one pass, but that can get messy.
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) + '%'Update MyTable
Set MyColumn = Left(MyColumn, Len(MyColumn)-1)
Where MyColumn Like '%' + Char(34) It could probably be done in one pass, but that can get messy.
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
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
@matthewspatrick,
Show off! :-p
Show off! :-p
ASKER
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"
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"
ASKER
oh how about:
update table
set column = case
WHEN column LIKE '%""' THEN LEFT(column, LEN(column) - 1)
ELSE column END
update table
set column = case
WHEN column LIKE '%""' THEN LEFT(column, LEN(column) - 1)
ELSE column END
ASKER
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 ""
1/8"" HEX NUT CLECO, 0-1/2"
dang -- need to find where there is a ""
That is the right results. You'll have to do an additional Update query in those cases
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.
Update MyTable
Set MyColumn = Replace(MyColumn, Char(34) + Char(34), Char(34))
Where MyColumn Like '%' + Char(34) + Char(34) + '%'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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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"".
Open in new window
The Char(34) is the ASCII for a double quote mark.