September 22, 2011 at 9:20 am
Hello all. I have a problem. A downloaded table includes a "T" character at the end of a string to mark where changes were made. I do not need that T, and in fact is confusing for my purposes. Using REPLACE removes all T's however, so while "Rice FarmingT" becomes "Rice Farming", "Fruit and Tree Nut FarmingT" becomes "Frui and ree Nu Farming". But not all the cells have T's at the end, so I can't just cut out the last letter.
Does anyone know how to get rid of only one letter? Thank you so much!
September 22, 2011 at 9:24 am
CASE WHEN Col LIKE '%T' THEN LEFT(Col, LEN(Col) - 1) ELSE Col END
September 22, 2011 at 9:27 am
Amy.G (9/22/2011)
Hello all. I have a problem. A downloaded table includes a "T" character at the end of a string to mark where changes were made. I do not need that T, and in fact is confusing for my purposes. Using REPLACE removes all T's however, so while "Rice FarmingT" becomes "Rice Farming", "Fruit and Tree Nut FarmingT" becomes "Frui and ree Nu Farming". But not all the cells have T's at the end, so I can't just cut out the last letter.Does anyone know how to get rid of only one letter? Thank you so much!
You could do a substring. Something like this:
declare @STR varchar(50)
set @STR = 'Rice FarmingT'
select substring(@str,1,len(@str)-1)
So the update would look like:
update yourtable set yourcolumn = substring(yourcolumn,1,len(yourcolumn)-1)
Hope that helps.
September 22, 2011 at 9:30 am
bkubicek (9/22/2011)
Amy.G (9/22/2011)
Hello all. I have a problem. A downloaded table includes a "T" character at the end of a string to mark where changes were made. I do not need that T, and in fact is confusing for my purposes. Using REPLACE removes all T's however, so while "Rice FarmingT" becomes "Rice Farming", "Fruit and Tree Nut FarmingT" becomes "Frui and ree Nu Farming". But not all the cells have T's at the end, so I can't just cut out the last letter.Does anyone know how to get rid of only one letter? Thank you so much!
You could do a substring. Something like this:
declare @STR varchar(50)
set @STR = 'Rice FarmingT'
select substring(@str,1,len(@str)-1)
So the update would look like:
update yourtable set yourcolumn = substring(yourcolumn,1,len(yourcolumn)-1)
Hope that helps.
That was my first solution too until I remembered not ALL rows end with a T, only the ones modified.
The real question is how to do differentiate with a T for modification and a T without modification.
September 22, 2011 at 9:41 am
Even though Ninja's_RGR'us solution should have worked, this is what ended up doing the trick --
Case when RIGHT(rtrim(f3),1) = 'T' then LEFT( f3, LEN(f3)-1) else f3 end
I would ponder why it was that Ninja's_RGR'us's solution removed the "T" from "Agriculture", but not from "Crop Production" but I have to let it go and move onto to the actual business problem.
Thank you all!
September 22, 2011 at 9:43 am
Yet another way: -
DECLARE @TABLE AS TABLE (col VARCHAR(100))
INSERT INTO @TABLE
SELECT 'Rice FarmingT'
UNION ALL SELECT 'Fruit and Tree Nut FarmingT'
UNION ALL SELECT 'Tree' --Non-modified row
UNION ALL SELECT 'Fruit' --Non-modified row ending with a 't'
SELECT CASE WHEN invalid = 1
THEN LEFT(col, LEN(col) - 1)
ELSE col END
FROM (SELECT PATINDEX('[T]%',REVERSE(col)) AS invalid, col
FROM @TABLE) a
September 22, 2011 at 9:46 am
Amy.G (9/22/2011)
Even though Ninja's_RGR'us solution should have worked, this is what ended up doing the trick --Case when RIGHT(rtrim(f3),1) = 'T' then LEFT( f3, LEN(f3)-1) else f3 end
I would ponder why it was that Ninja's_RGR'us's solution removed the "T" from "Agriculture", but not from "Crop Production" but I have to let it go and move onto to the actual business problem.
Thank you all!
It shouldn't have. I don't use replace. I use left with stop at next to last character.
Are you sure you didn't use LIKE '%T%'? The last % shouldn't be there.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply