August 8, 2012 at 8:46 pm
Comments posted to this topic are about the item Trim Left Char (for single words)
August 16, 2012 at 2:26 am
This does have a slight flaw:
Any spaces in the original string get replaced with the character you're trying to trim.
E.g. The result of
Print [dbo].[TrimLeftChar]( '.', '...will i am')
will be:
'will.i.am'
And not:
'will i am'
This version will preserve the original string (explanation in comments):
CREATE FUNCTION [dbo].[TrimLeftChar]( @CharToTrim AS CHAR(1) ,
@String AS VARCHAR(MAX)
) RETURNS VARCHAR(MAX)
AS
BEGIN
RETURN RIGHT(@String, LEN(LTRIM( REPLACE(@String, @CharToTrim,' '))))
-- 1) Replace @CharToTrim occurrences with spaces
-- 2) Trim the spaces
-- 3) Get the length of the resultant string
-- 4) Use the length to take the right-most chars from the original.
END
One more minor quibble: if I was going to use this in production code I'd also avoid VarChar(Max) unless it was definitely required - there's quite a performance hit for using it (even where the actual string lengths passed in are small).
Ian
August 16, 2012 at 2:44 am
...oops just realised one further thing: my version doesn't work correctly where there are trailing spaces, because the LEN function ignores them.
This fixes that by adding a character to the end of the string to force LEN to count the spaces:
RIGHT(@String, LEN(LTRIM( REPLACE(@String, @CharToTrim,' ') + '.')) - 1)
Ian
August 16, 2012 at 5:08 am
Rather than using the REPLACE, TRIM and REPLACE again, what do you think of the following:
CREATE FUNCTION dbo.TrimLeftChar
(
@Source VARCHAR(1000),
@Char CHAR(1)
)
RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @Pattern VARCHAR(6)
SET @Pattern = '%[^' + @Char + ']%'
RETURN SUBSTRING(@Source, PATINDEX(@Pattern, @Source), LEN(@Source))
END
August 16, 2012 at 8:59 am
Hi friends,
I really like both versions.
About the len function, maybe the datalength function can do the job too.
select RIGHT(@String, datalength (LTRIM( REPLACE(@String, @CharToTrim,' '))))
Thanks again for you contributions.
August 16, 2012 at 9:24 am
Hi again,
Here it is the TrimRight versions:
CREATE FUNCTION [dev].[TrimRightChar]
( @CharToTrim AS CHAR(1) ,
@String AS VARCHAR(MAX)
) RETURNS VARCHAR(MAX)
AS
BEGIN
-- icocks from SqlServerCentral.com and me.
RETURN LEFT(@String, DATALENGTH(RTRIM( REPLACE(@String, @CharToTrim,' '))))
-- 1) Replace @CharToTrim occurrences with spaces
-- 2) Trim the spaces
-- 3) Get the length of the resultant string
-- 4) Use the length to take the LEFT-most chars from the original.
END
CREATE FUNCTION dbo.[TrimRightChar]
(
@Source VARCHAR(1000),
@Char CHAR(1)
)
RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @Pattern VARCHAR(6)
SET @Pattern = '%[^' + @Char + ']%'
RETURN LEFT(@Source,DATALENGTH(@Source) - PATINDEX(@Pattern, REVERSE(@Source)) + 1 )
END
May 10, 2016 at 6:54 am
Thanks for the script.
May 10, 2016 at 6:55 am
...but I probably won't use it. Thanks for it anyway. It always makes you think working through these scripts.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply