Use of the trim () function to delete the left and right characters in SQL statements

The Trim function in the program is known to all, but in SQL, only LTRIM is required. RTRIM deletes the left and right blank characters instead of the specified characters, so we can write one by ourselves.

Requirements:

1. The front and back spaces can be deleted, for example, 'A'-> 'A'

2. The front and back characters can be deleted and are not affected by blank spaces, such as '; a'-> 'A'

3. After the front and back characters are deleted, the front and back spaces must be cleared, such as '; a'-> 'A'

4. You need to delete consecutive characters, such as '; a'-> 'A'

I think it is quite good that some others write on the Internet, but it does not seem to fully meet the requirements, so I wrote one by myself.

The code for creating a function is as follows:

The code is as follows:

Copy code

/****** Object: UserDefinedFunction [dbo]. [TRIM] Script Date: 09:10:14 Author: EF ******/ If exists (SELECT * FROM sys. objects WHERE object_id = OBJECT_ID (n' [dbo]. [trim] ') AND type in (N 'Fn', N 'if', N 'tf', N 'Fs', N 'FT ')) Drop function [dbo]. [trim] GO Create function dbo. trim ( @ Source VARCHAR (MAX ), @ Char CHAR (1) ) Returns varchar (MAX) AS BEGIN DECLARE @ I int; DECLARE @ returnString VARCHAR (MAX) SET @ returnString = @ Source -- Clear leading and trailing spaces SET @ returnString = LTRIM (RTRIM (@ returnString )) -- Delete characters on the left SET @ I = 0 WHILE @ I = 0 BEGIN If left (@ returnString, 1) = @ Char SET @ returnString = RIGHT (@ returnString, LEN (@ returnString)-1) ELSE SET @ I = 1 END -- Delete characters on the right SET @ I = 0 WHILE @ I = 0 BEGIN If right (@ returnString, 1) = @ Char SET @ returnString = LEFT (@ returnString, LEN (@ returnString)-1) ELSE SET @ I = 1 END -- Clear leading and trailing spaces SET @ returnString = LTRIM (RTRIM (@ returnString )) RETURN @ returnString; END GO -- Test Select dbo. trim ('asdfas; asdfasdfa ;',';') Union all select dbo. trim ('; asdfas; asdfasdfa ;',';') Union all select dbo. trim ('; asdfas; asdfasdfa ;',';') Union all select dbo. trim ('; asdfas; asdfasdfa ;',';')   -- Result ------------------------------------------------------------------------- Asdfas; asdfasdfa Asdfas; asdfasdfa Asdfas; asdfasdfa Asdfas; asdfasdfa (Four rows affected)