Have someone a function for SQL Server that given a pattern and a value could return a normalized value -
i'm programming function in sql 2008r2 give parameters value varchar, pattern varchar, separator char , filler char. give value '22687' patter '000.000.000.000', separator '.' , filler '0', expect function return '000.000.022.687', 1 have function done can this?
something this:
declare @valor varchar(30) declare @formato varchar(30) declare @separador char(1) declare @rellenarcon char(1) select @valor = '22959' select @formato = '000.000.000.000' select @separador = '.' select @rellenarcon = '0' declare @n integer declare @m integer declare @i integer declare @j integer select @n = len(@formato) select @m = len(@valor) select @i = 1 select @j = 1 declare @res2 varchar(30) select @res2 = '' select @valor = reverse(@valor) while @i<=@n begin if substring(@formato,@i,1) <> @separador begin if @j<=@m begin select @res2 = @res2 + substring(@valor,@j,1) select @i=@i+1 select @j=@j+1 end else begin select @res2 = @res2 + @rellenarcon select @i=@i+1 end end else begin select @res2 = @res2 + @separador select @i=@i+1 end end print reverse(@res2) is crossover code java tsql, original code in java is:
public static string formatear(string valor, string formato, char separator, char fillwith, map<integer, string> params) { int n = formato.length() - 1; int m = valor.length() - 1; int = n; int j = m; stringbuilder res = new stringbuilder(formato); for(; >= 0; i--) { if(res.charat(i) != separator) { if(j >= 0) { res.deletecharat(i); res.insert(i, valor.charat(j--)); } else { res.deletecharat(i); res.insert(i, fillwith); } } } if(params != null) { set<integer> keys = params.keyset(); for(integer key : keys) { = key; res.deletecharat(i); res.insert(i, params.get(key)); } } return res.tostring(); }
the following assumes well-formed inputs, e.g. value not longer pattern.
declare @pattern varchar(64) = '000.000.000.000'; declare @fill char = '0'; declare @value varchar(64) = '22687'; declare @false bit = 0; declare @true bit = 1; gargoyle ( select @pattern pattern, @value value, cast( '' varchar(64) ) buffer, case when right( @pattern, 1 ) = @fill @true else @false end fill union select -- consume character pattern. left( pattern, len( pattern ) - 1 ), -- consume character value if pattern contains fill @ current position. case when fill = @true , value != '' left( value, len( value ) - 1 ) else value end, -- add correct character buffer. cast( case when fill = @true , value != '' right( value, 1 ) else right( pattern, 1 ) end + buffer varchar(64) ), -- check next pattern character fill. case when len( pattern ) = 1 @false when substring( pattern, len( pattern ) - 1, 1 ) = @fill @true else @false end gargoyle pattern != '' ) select buffer gargoyle pattern = ''; or, function:
create function dbo.patternfill( @pattern varchar(64), @fill char, @value varchar(64) ) returns varchar(64) begin declare @buffer varchar(64) = '' declare @patternchar char = right( @pattern, 1 ) declare @valuechar char = right( @value, 1 ) while @pattern != '' begin if @patternchar = @fill , @valuechar != '' begin -- replace fill character value character. select @buffer = @valuechar + @buffer if len( @value ) > 1 select @value = left( @value, len( @value ) - 1 ), @valuechar = right( @value, 1 ) else select @valuechar = '', @value = '' end else begin -- copy pattern character. select @buffer = @patternchar + @buffer end if len( @pattern ) > 1 select @pattern = left( @pattern, len( @pattern ) - 1 ), @patternchar = right( @pattern, 1 ) else select @patternchar = '', @pattern = '' end return @buffer end go declare @result varchar(64) declare @count int = 1000000 declare @start datetime = getdate() while @count > 0 select @result = dbo.patternfill( '000.000.000.000', '0', '22687' ), @count = @count - 1 select @result [result], datediff( ms, @start, getdate() ) [total ms] 1,000,000 iterations on notebook took 151,656ms, it's busy boincing. that's simple timing no correction time consumed empty loop or calling empty function.
Comments
Post a Comment