"SQL" a reliable trim function, a bonus process

Original: "SQL" a reliable trim function, a bonus process

SQL has LTrim and RTrim These two functions are used to remove the first and trailing whitespace of the string, the absence of common trim functions can be removed at the same time, and the two functions are only valid for "space", so if the end is a tab, newline characters and so on "blank", they are not processed ~ at least to SQL 2k8 R2 still so. In view of this, I wrote a reliable trim function, on the code:

/*---------------------Function: Remove the V0.01author:ahdungupdate:201311301716rem: Because Len does not count trailing blanks, So do special handling---------------------*/ALTER FUNCTIONDbo. Trim (@s VARCHAR(7999))RETURNS VARCHAR(7999) asBEGIN while ASCII( Right(@s,1))inch(9,Ten, -, +)BEGINSET @s=left(@s, Case ASCII( Right(@s,1)) when  +  Then LEN(@s)ELSE LEN(@s)-1 END)END while ASCII(@s)inch(9,Ten, -, +)BEGINSET @s=right(@s,LEN(@s)-1)ENDRETURN @sEND

The beginning of the idea is to start from the first character string to traverse judgment, if "Space, tab, carriage return, newline", then remove, and then from the far right to come again. But the fucking Len function does not count trailing blanks, so change from the right to start the traversal, and went ahead use Len this motherfucker set to remove the right space, finished again to deal with the left.

Also included is a procedure based on this function to remove whitespace from a specified table field, optionally removing intermediate blanks (default is no removal):

/*-----------------------------------Process: Removes the v0.01author:ahdungupdate:201311301720 of the specified table field------------------- ----------------*/ALTER PROCPtrimfield@TbNamesysname,@ColNamesysname,@RemoveAll BIT = 0 --whether to remove the middle white space. Default = no asSETNOCOUNT onDECLARE @sql VARCHAR( +)= 'UPDATE ['+@TbName+']set ['+@ColName+'] = dbo.'+ Case @RemoveAll  when 0  Then 'Trim' ELSE 'Removeallwhitespace' END+'(['+@ColName+']) WHERE PATINDEX ("'%["'+char (+) +char (9) +char (Ten) +char (+"']%"',['+@ColName+']) >0'EXEC(@sql)

This process is useful for some fields that allow users to fill out, but do not process in the program when writing. In addition, the process uses the TRIM function above, and another function called Removeallwhitespace, to remove all whitespace in the string, the function is also attached:

/*---------------------Function: Remove all blank v0.01author:ahdungupdate:201311301716---------------------*/CREATE FUNCTIONDbo. Removeallwhitespace (@s VARCHAR(7999))RETURNS VARCHAR(7999) asBEGINRETURN REPLACE(REPLACE(REPLACE(REPLACE(@s,CHAR( +),"'),CHAR(9),"'),CHAR(Ten),"'),CHAR( -),"')END

Wenbi.

"SQL" a reliable trim function, a bonus process