07-31-2023, 09:47 AM
It seems that everybody keeps referring to a single REPLACE function. Or even many calls of a REPLACE function. But when you have dynamic output with an unknown number of spaces, it wont work. Anybody that deals with this issue on a regular basis knows that REPLACE will only remove a single space, NOT ALL, as it should. And LTRIM and RTRIM seem to have the same issue. Leave it to Microsoft.
Here's a sample output that uses a WHILE Loop to remove ALL CHAR(32) values (space).
DECLARE @INPUT_VAL VARCHAR(8000)
DECLARE @OUTPUT_VAL VARCHAR(8000)
SET @INPUT_VAL = ' C A '
SET @OUTPUT_VAL = @INPUT_VAL
WHILE CHARINDEX(CHAR(32), @OUTPUT_VAL) > 0 BEGIN
SET @OUTPUT_VAL = REPLACE(@INPUT_VAL, CHAR(32), '')
END
PRINT 'START:' + @INPUT_VAL + ':END'
PRINT 'START:' + @OUTPUT_VAL + ':END'
Here's the output of the above code:
START: C A :END
START:CA:END
Now to take it a step further and utilize it in an UPDATE or SELECT statement, change it to a udf.
CREATE FUNCTION udf_RemoveSpaces (@INPUT_VAL VARCHAR(8000))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @OUTPUT_VAL VARCHAR(8000)
SET @OUTPUT_VAL = @INPUT_VAL
-- ITTERATE THROUGH STRING TO LOOK FOR THE ASCII VALUE OF SPACE (CHAR(32)) REPLACE IT WITH BLANK, NOT NULL
WHILE CHARINDEX(CHAR(32), @OUTPUT_VAL) > 0 BEGIN
SET @OUTPUT_VAL = REPLACE(@INPUT_VAL, CHAR(32), '')
END
RETURN @OUTPUT_VAL
END
Then utilize the function in a SELECT or INSERT statement:
UPDATE A
SET STATUS_REASON_CODE = WHATEVER.dbo.udf_RemoveSpaces(STATUS_REASON_CODE)
FROM WHATEVER..ACCT_INFO A
WHERE A.SOMEVALUE = @SOMEVALUE
INSERT INTO SOMETABLE
(STATUS_REASON_CODE)
SELECT WHATEVER.dbo.udf_RemoveSpaces(STATUS_REASON_CODE)
FROM WHATEVER..ACCT_INFO A
WHERE A.SOMEVALUE = @SOMEVALUE
Here's a sample output that uses a WHILE Loop to remove ALL CHAR(32) values (space).
DECLARE @INPUT_VAL VARCHAR(8000)
DECLARE @OUTPUT_VAL VARCHAR(8000)
SET @INPUT_VAL = ' C A '
SET @OUTPUT_VAL = @INPUT_VAL
WHILE CHARINDEX(CHAR(32), @OUTPUT_VAL) > 0 BEGIN
SET @OUTPUT_VAL = REPLACE(@INPUT_VAL, CHAR(32), '')
END
PRINT 'START:' + @INPUT_VAL + ':END'
PRINT 'START:' + @OUTPUT_VAL + ':END'
Here's the output of the above code:
START: C A :END
START:CA:END
Now to take it a step further and utilize it in an UPDATE or SELECT statement, change it to a udf.
CREATE FUNCTION udf_RemoveSpaces (@INPUT_VAL VARCHAR(8000))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @OUTPUT_VAL VARCHAR(8000)
SET @OUTPUT_VAL = @INPUT_VAL
-- ITTERATE THROUGH STRING TO LOOK FOR THE ASCII VALUE OF SPACE (CHAR(32)) REPLACE IT WITH BLANK, NOT NULL
WHILE CHARINDEX(CHAR(32), @OUTPUT_VAL) > 0 BEGIN
SET @OUTPUT_VAL = REPLACE(@INPUT_VAL, CHAR(32), '')
END
RETURN @OUTPUT_VAL
END
Then utilize the function in a SELECT or INSERT statement:
UPDATE A
SET STATUS_REASON_CODE = WHATEVER.dbo.udf_RemoveSpaces(STATUS_REASON_CODE)
FROM WHATEVER..ACCT_INFO A
WHERE A.SOMEVALUE = @SOMEVALUE
INSERT INTO SOMETABLE
(STATUS_REASON_CODE)
SELECT WHATEVER.dbo.udf_RemoveSpaces(STATUS_REASON_CODE)
FROM WHATEVER..ACCT_INFO A
WHERE A.SOMEVALUE = @SOMEVALUE