cool hit counter [SQL] Reliable TRIM function, with a free procedure_Intefrankly

[SQL] Reliable TRIM function, with a free procedure


SQL has LTRIM and RTRIM these two functions are used to remove the first and last spaces of the string, the lack of common TRIM function to remove both the first and last, in addition, these two functions are only valid for [space], so if the first and last are tabs, newlines, etc. [blank], they are not handled ~ at least to sql 2k8 r2 is still the case. With this in mind, I wrote a reliable TRIM function, on code.

/*---------------------
 Function: Remove head and tail blanks v0.01
Author:AhDung
Update:201311301716
 Rem:Special treatment because LEN does not count trailing spaces
---------------------*/
ALTER FUNCTION dbo.Trim(@s VARCHAR(7999))
RETURNS VARCHAR(7999) AS
BEGIN
WHILE ASCII(RIGHT(@s,1)) IN (9,10,13,32)
BEGIN
SET @s=LEFT(@s, CASE ASCII(RIGHT(@s,1)) WHEN 32 THEN LEN(@s) ELSE LEN(@s)-1 END)
END
WHILE ASCII(@s) IN (9,10,13,32)
BEGIN
SET @s=RIGHT(@s,LEN(@s)-1)
END
RETURN @s
END

The idea at first is to start traversing the string from the first character to determine if it is a [space, tab, carriage return, newline], remove it, and then come back through from the far right. But the fucked up LEN function doesn't count trailing spaces, so instead we start traversing from the right and will use the fucked up LEN setting to remove the right spaces, and then deal with the left when we're done.

Also attached is a procedure based on this function that serves to remove whitespace from the specified table fields, optionally removing intermediate whitespace (the default is no removal).

/*-----------------------------------
 Procedure: remove head and tail blanks from specified table fields v0.01
Author:AhDung
Update:201311301720
-----------------------------------*/
ALTER PROC PTrimField
  @TbName sysname
, @ColName sysname
 , @RemoveAll BIT = 0 - whether to remove the middle margin. Default = No
AS
SET NOCOUNT ON
DECLARE @sql VARCHAR(1000) = 'UPDATE ['+@TbName+']
SET ['+@ColName+'] = dbo.'+CASE @RemoveAll WHEN 0 THEN 'Trim' ELSE 'RemoveAllWhiteSpace' END+'(['+@ColName+'])
WHERE PATINDEX(''%[''+CHAR(32)+CHAR(9)+CHAR(10)+CHAR(13)+'']%'',['+@ColName+'])>0'

EXEC (@sql)

This procedure is useful for trimming some fields that are allowed to be filled in by the user but are not done in the program when they are written. In addition, the process uses another function called RemoveAllWhiteSpace to remove all whitespace from the string, in addition to the TRIM function above, which is also attached.

/*---------------------
 function: Remove all blanksv0.01
Author:AhDung
Update:201311301716
---------------------*/
CREATE FUNCTION dbo.RemoveAllWhiteSpace(@s VARCHAR(7999))
RETURNS VARCHAR(7999) AS
BEGIN
RETURN REPLACE(REPLACE(REPLACE(REPLACE(@s,CHAR(32),''),CHAR(9),''),CHAR(10),''),CHAR(13),'')
END

Text Bi.


Recommended>>
1、WeChat grab red packet cheats wechat grab red packet hangers wechat grab red packet auxiliary seethrough software
2、Foreign media exposed Huawei P20 series more detailed information with the previous netizens out of the mysterious table content consistent
3、Issue 83 One month into the public beta of Coinfest what am I unhappy about
4、This 400000 of land in Shunyi will be used to build rental housing
5、Talking about all aspects of timers from the design of a timeout program

    已推荐到看一看 和朋友分享想法
    最多200字,当前共 发送

    已发送

    朋友将在看一看看到

    确定
    分享你的想法...
    取消

    分享想法到看一看

    确定
    最多200字,当前共

    发送中

    网络异常,请稍后重试

    微信扫一扫
    关注该公众号