// SQL function to trim enters (up to 6 enters at start of string)
// To use
// select TrimEnters(StrValue) from thatTable
CREATE FUNCTION dbo.TrimEnters(@string NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
BEGIN
declare @Ent1 as varchar(4) = CHAR(13) + CHAR(10)
declare @str54 as nvarchar(max), $str55 as nvarchar(max)
set @str54 = Replace(@string, @Ent1 + @Ent1 + @Ent1 + @Ent1 + @Ent1 + @Ent1, @Ent1) /* 6 Enters */
set @str54 = Replace(@str54, @Ent1 + @Ent1 + @Ent1 + @Ent1 + @Ent1, @Ent1) /* 5 Enters */
set @str54 = Replace(@str54, @Ent1 + @Ent1 + @Ent1 + @Ent1, @Ent1) /* 4 Enters */
set @str54 = Replace(@str54, @Ent1 + @Ent1 + @Ent1, @Ent1) /* 3 Enters */
set @str54 = Replace(@str54, @Ent1 + @Ent1, @Ent1) /* 2 Enters */
SELECT @str55 = CASE WHEN @str54 LIKE @Ent1 + '%' /* Enter found at start of string */
THEN Right(@str54, 3)
ELSE @str54
END;
RETURN LTRIM(RTRIM(@str54))
END
GO
// To use
// select TrimEnters(StrValue) from thatTable
CREATE FUNCTION dbo.TrimEnters(@string NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
BEGIN
declare @Ent1 as varchar(4) = CHAR(13) + CHAR(10)
declare @str54 as nvarchar(max), $str55 as nvarchar(max)
set @str54 = Replace(@string, @Ent1 + @Ent1 + @Ent1 + @Ent1 + @Ent1 + @Ent1, @Ent1) /* 6 Enters */
set @str54 = Replace(@str54, @Ent1 + @Ent1 + @Ent1 + @Ent1 + @Ent1, @Ent1) /* 5 Enters */
set @str54 = Replace(@str54, @Ent1 + @Ent1 + @Ent1 + @Ent1, @Ent1) /* 4 Enters */
set @str54 = Replace(@str54, @Ent1 + @Ent1 + @Ent1, @Ent1) /* 3 Enters */
set @str54 = Replace(@str54, @Ent1 + @Ent1, @Ent1) /* 2 Enters */
SELECT @str55 = CASE WHEN @str54 LIKE @Ent1 + '%' /* Enter found at start of string */
THEN Right(@str54, 3)
ELSE @str54
END;
RETURN LTRIM(RTRIM(@str54))
END
GO
StrValue
select TrimEnters(column) from thatTable
Views 811
Downloads 334
CodeID
DB ID