Replace like in SQL Server 2008 R2 -
i have replace give string specific format.
example:
given string is:
between 0 , 0:15
replace into:
between '00:00:00' , '00:15:00'
my attempt:
declare @tm varchar(max) = 'between 0 , 0:15' declare @rep varchar(max) set @rep = replace(@tm,'0','''00:00:00'''); print(@rep);
getting result:
between '00:00:00' , '00:00:00':15
first answer it, believe want build sql string, query include quotes
declare @tm varchar(max) = 'between 0 , 0:15' declare @st varchar(10), @et varchar(10) declare @rep varchar(max) --set @rep = replace(@tm,'0','''00:00:00'''); set @tm = ltrim(rtrim(substring(@tm, charindex(' ', @tm), len(@tm)))) -- remove between set @st = ltrim(rtrim(substring(@tm, 1, charindex(' ', @tm)))) -- start time set @tm = ltrim(rtrim(substring(@tm, charindex(' ', @tm) + 4, len(@tm)))) -- remove , set @et = ltrim(rtrim(@tm)); set @rep = 'between ''' + convert(varchar(8), convert(time, case when isnumeric(@st) = 1 replace(@st,'0','00:00:00') else @st end)) + ''' , ''' + convert(varchar(8), convert(time, case when isnumeric(@et) = 1 replace(@et,'0','00:00:00') else @et end)) + '''' print(@rep);
i showed each step of removing words, , didn't assume between
or first time can 0
last 1 too
Comments
Post a Comment