Trim custom characters in a SQL string : SQL Feeds

Trim custom characters in a SQL string

by David L. Penton via The Penton-izer on 10/15/2008 11:04:00 PM

Someone on one of the SQL Server lists I subscribe to proposed a question earlier today - How do you trim leading and trailing characters? In this case, the desired string was a numerical string, and the character to trim was a period. There were several responses to that question.   I felt compelled to write a blog post afterwards.

declare @s varchar(20)

set @s = '...1234.56...'

select
    substring( @s
        /* where do we start this search */
        , patindex( '%[0-9]%', @s )
        /* must start with the length of the string */
        , len(@s)
            /* remove the length of the beginning items that are not numbers */
            - ( patindex( '%[0-9]%', @s ) - 1 )
            /* remove the length of the ending items that are not numbers */
            - ( patindex( '%[0-9]%', reverse( @s ) ) - 1 )
        )