Twitter

Current Events

Other Activities

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...'

selectsubstring( @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?

This entry was posted
on Wednesday, October 15 2008 and is filed under SQL Server.