I had faced
white space problem writing algorithm of search in SQL Query, I had applied there
FTS(Full-Text Search) when a text comes with extra space, my search becomes failed.
I applied there LTRIM and RTRIM but did not get success then I applied bellow
approach and gain victory over there.
DECLARE @str VARCHAR(200)
DECLARE @expres VARCHAR(50) = SPACE(2)
SET @str = 'I am
trying to remove
extra space'
WHILE CHARINDEX( @expres, @str ) > 0
BEGIN
SET @str = REPLACE( @str, @expres,' ')
END
SELECT @str
Above query, you can use as a function or don’t want
use as a function and want to apply direct approach see below code.
-- Sample query to remove extra space
DECLARE @str VARCHAR(200)='I am
trying to remove
extra space'
SELECT REPLACE (REPLACE(REPLACE(@str,' ','<>'),'><',''),'<>',' ')