I wanted to remove following spaces and special characters from these strings;
'a. Personnel '
'j. Indirect Charges '
I tried to use following SQL to get rid of all spaces;
SELECT DISTINCT TRIM(LOWER(REPLACE(BudgetCategories, ' ', ''))) BudgetCategories
FROM [dbo].[MyTable]
Still the trailing spaces were there. I guess there is a special character involved, so i tried this one;
SELECT DISTINCT LTRIM(RTRIM(LOWER((REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(BudgetCategories, ' ', ''), CHAR(10), CHAR(32)),CHAR(13), CHAR(32)),CHAR(160), CHAR(32)),CHAR(9),CHAR(32))))))
FROM [dbo].[MyTable]
This did the trick and I was able to get this output;
'a.personnel'
'j.indirectcharges'
References
https://stackoverflow.com/questions/21585914/trim-spaces-in-string-ltrim-rtrim-not-working
Add to favorites
