Thursday, October 27, 2011

Replacing multiple occurences of space with one space in a string

Given string :  THIS                IS                A                 BAD                 STRING
Out put string : THIS IS A BAD STRING

The Logic
-----------------
  • In the given string replcae the space with a token (e,g. ~ in this case) and a space  e.g. '~ '
  • Repace the space and token in the string result from step 1 with  an empty string.' ~'
  • Replace the result of the above atring with a token and the space
DECLARE
@str AS VARCHAR(1000) = 'THIS IS A BAD STRING',@Token AS VARCHAR(1) = '~'

SELECT REPLACE(REPLACE(REPLACE(@str,' ','~ '),' ~',''),'~',' ')

No comments:

Post a Comment