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 |
|
saoco
Starting Member
3 Posts |
 Posted - 2011-09-27 : 14:27:59
|
| Hello,I have one field called FeeDesc varchar(639) which I need to split into a number of sub-fields. I can do this without problem using SUBSTRING(OfferDesc, 1, 81) AS OFFERDESC1, SUBSTRING(FeeDesc, 82, 81) AS FEEDESC2 , etc. The problem is that the data from these split fields can contain a comma at the very end of the field. For example:PREMIER $72.99 ,I need to trim the white space between $72.99 and the comma, so the final output should be:PREMIER $72.99,FEEDESC2 is varchar(80)Can someone please let me know how I can accomplish this? Obviously, rtrim() alone won't work (since there's a comma at the end).TIA |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
saoco
Starting Member
3 Posts |
Posted - 2011-09-27 : 15:37:32
|
| No, because there may be commas in the field that I need to keep. I only need to delete all whitespace that precedes a comma *only* if a comma is in the last position of the field. Make sense? |
 |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2011-09-27 : 15:54:37
|
| perhaps this:case when right(FEEDESC2, 2) = ' ,' then stuff(FEEDESC2, len(FEEDESC2)-1, 1, '') else FEEDESC2 endBe One with the OptimizerTG |
|
|
|
saoco
Starting Member
3 Posts |
Posted - 2011-09-27 : 18:56:02
|
| I was able to do this:UPDATE myTable SETFEEDESC1 = CASE WHEN 0 < PATINDEX('% ,', FEEDESC1) THEN RTrim(LEFT(FEEDESC1, PATINDEX('% ,', FEEDESC1))) + ',' ELSE FEEDESC1 ENDand so far so good. Can you guys think of any issues with this? |
|
|
|
|
|
|
|
|
Help manipulating string