March 22, 2011 at 10:58 am
Hello All
Lets say I have a column with some "unwanted" characters on the end I might want to remove, how would you suggest I do this?
Example:
create table test
(
col varchar(10)
)
insert test(col)
select 'testvalue**'
union all
select 'testvalue**'
union all
select 'testvalue***'
I'd like to strip the asterisk values from the end?
As you can see there is no telling how many asterisks you can have?
March 22, 2011 at 11:09 am
With the values you've given, LEFT() would do what you ask for.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 22, 2011 at 12:32 pm
If it's always '*' that you want to remove, you could look at the REPLACE function.
March 22, 2011 at 1:21 pm
Thanks for your responses.
The only thing is I want to replace the character(s) only if it occurs at the end so 'opobo' strip away 'o' should become 'opob'
March 22, 2011 at 2:02 pm
So how are you defining the characters you want to keep vs the characters you want to remove?
March 22, 2011 at 2:15 pm
Try this:
IF OBJECT_ID('test') > 0
DROP TABLE test
GO
CREATE TABLE test (col VARCHAR(20))
GO
INSERT test
(
col
)
SELECT 'testvalue**'
UNION ALL
SELECT 'testvalue**'
UNION ALL
SELECT 'testvalue***'
GO
SELECT REVERSE(SUBSTRING(REVERSE(col), PATINDEX('%[^*]%', REVERSE(col)), 20))
FROM test
GO
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 23, 2011 at 4:18 am
Thanks.
This does the trick 🙂
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply