Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
alys
Starting Member
5 Posts |
 Posted - 2002-07-22 : 05:29:43
|
| Some text fields - varchar and memo, have a number of enter characters on the end of them. How can I remove them? |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
|
|
alys
Starting Member
5 Posts |
Posted - 2002-07-22 : 06:18:37
|
| thanks andrew,is there a way i could combine these functions (eg replace) with some kind of RTrim function - but specifically for my special characters? I only want to remove chars from the right side of the text string. Is there an easy way to do this?Thanks |
 |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2002-07-22 : 11:44:51
|
| you could do a concat(leftside of string, replace(rightside of string))where left+right sides are mutually exclusive but combined make up the source string...only issue for you is deciding where to split the column into 2 parts....if it's fixed then you can go something like....concat(left(texta,50),replace(right(texta,10),"a","b"))which will split a 60 char field into 1st 50 (untreated) and 2nd 10...in which all a's get replaced by b's....if the 'to be replaced stuff' isn't always in the same space....but is 'close to the end'...(where close is a relative term)....then you can do a 'find'...searching the string from the right....have a look at charindex.... |
|
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2002-07-22 : 12:48:52
|
| something like this? (varchar only)LEFT(col, LEN(col) - CHARINDEX(RIGHT(REPLACE('x'+col, CHAR(13)+CHAR(10), ''), 1), REVERSE('x'+col)) + 1)if you wanted to remove them from the front like LTRIM, you would have to move the 'x' that's acting as a sentinel to the other end:RIGHT(col, LEN(col) - CHARINDEX(LEFT(REPLACE(col+'x', CHAR(13)+CHAR(10), ''), 1), col+'x') + 1)Edited by - Arnold Fribble on 07/22/2002 13:01:16 |
|
|
|
|
|
|
|
|
removing enter characters