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?
2008-10-15 10:09:01
Hmmm .. what is "propsed"
2008-10-15 10:15:11
A misspelled word. Thanks Eric :)
2009-05-07 05:34:58
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, '|'),'.',''),'|','.')
2012-07-20 04:02:09
Awesome! Just what I needed. Thanks!
2014-03-17 04:56:06
This is helpful information. Thanks a lot!