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...'
/* where do we start this search */
, patindex( '%[0-9]%', @s )
/* must start with the length of the string */
/* 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?