Clear Thinking in Data, Cloud, and AI

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