程序中的Trim函数大伙都知道的,但是要SQL中只有LTRIM,RTRIM删除左、右空白字符,而不能删除指定字符,所以我们自己写一个。
要求:
1. 能删除前后空白,如 ' aa ' -> 'aa'
2. 能删除前后字符,并不受空白影响,如 ' ;aa' -> 'aa'
3. 删除前后字符后,需清除前后空格,如 '; aa' -> 'aa'
4. 需删除前后连续的字符,如 ';;;aa' -> 'aa'
网上也有一些别人写的,我觉得很不错,不过貌似没有完整的能达到要求的,所以自己动手写了一个。
创建函数代码如下:
/****** Object: UserDefinedFunction [dbo].[TRIM] Script Date: 11/18/2011 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 --清除前后空格 SET @returnString = LTRIM(RTRIM(@returnString)) --删除左侧字符 SET @i = 0 WHILE @i=0 BEGIN IF LEFT(@returnString,1) = @Char SET @returnString = RIGHT(@returnString,LEN(@returnString)-1) ELSE SET @i=1 END --删除右侧字符 SET @i = 0 WHILE @i=0 BEGIN IF RIGHT(@returnString,1) = @Char SET @returnString = LEFT(@returnString,LEN(@returnString)-1) ELSE SET @i=1 END --清除前后空格 SET @returnString = LTRIM(RTRIM(@returnString)) RETURN @returnString; END GO --测试 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;',';') --结果 ------------------------------------------------------------------------- asdfas;asdfasdfa asdfas;asdfasdfa asdfas;asdfasdfa asdfas;asdfasdfa (4 行受影响)