Trim custom characters in a SQL string

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 )
       
)

How would you do it?

5 Comments

  1. Eric Says:

    Hmmm .. what is "propsed"

  2. David L. Penton Says:

    A misspelled word. Thanks Eric :)

  3. M A Srinivas Says:

    Assuming Number has always having a decimal point like 1234.56 and number are not coming like 123456

    declare @s varchar(20)

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

    select replace(replace(stuff(@s,patindex( '%[0-9].%[0-9]%', @s ) + 1,1, '|'),'.',''),'|','.')

  4. Carter Says:

    Awesome! Just what I needed. Thanks!

  5. Mia Says:

    This is helpful information. Thanks a lot!


Leave a Reply