Menu
I've got a data-set of people's names but the problem is I imagine when some people were typing in their names they hit the spacebar a few times too many b/c now we have this:
Notice how in the name column there're some names like John_Doe, John__Doe, John____Doe, etc. What would be the best way to ensure that whenever there's a _ between words, be it 1,2,3, etc. it's removed/trimmed to only 1 space so all of these records would become John_Doe.
Thoughts?
876
DECLARE @Demo TABLE(OriginalString VARCHAR(8000)) INSERT INTO @Demo (OriginalString) SELECT ' This has multiple unknown spaces in it. ' UNION ALL SELECT 'So does this! ' UNION ALL SELECT 'As does this' UNION ALL SELECT 'This, that, and the other thing. ' UNION ALL SELECT 'This needs no repair.
When the right side of a LIKE predicate expression features a value with a trailing space, SQL Server does not pad the two values to the same length before the comparison occurs.
SQL Server does not support for Trim() function. But you can use LTRIM() to remove leading spaces and RTRIM() to remove trailing spaces. can use it as LTRIM(RTRIM(ColumnName)) to remove both.
This should do the trick
DECLARE @string varchar(100)
SET @string = 'John Doe'
SELECT string = REPLACE(REPLACE(REPLACE(@string,' ','<>'),'><',''),'<>',' ')
Replace duplicate spaces with a single space in T-SQL
109
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
