Decoding Numara Footprints special characters

My client wants to query and run report against the Footprints database, which is a Microsoft SQL server. It was simple enough until we noticed the data have some kind of encoding, a blank space will appear to be “__b”. This applies to all the records and fields. After contacting the support, I was given a list of all the encoding values.

To be able to decode the data in a query, I built an user-defined function to simplify the conversion for me. A quick and dirty solution.

Note: the collate clause tells the SQL server to compare string in case-sensitive manner.

CREATE function [dbo].[udf_decodeFootprintsStringValue] (@pFootprintValue varchar(2000)) 
returns varchar(2000)
as
	begin
		declare @pMappedValue varchar(2000)
		set @pMappedValue = replace(
							replace(
							replace(
							replace(
							replace(
							replace(
							replace(
							replace(
							replace(
							replace(
							replace(
							replace(
							replace(
							replace(
							replace(
							replace(
							replace(
							replace(
							replace(
							replace(
							replace(
							replace(
							replace(
							replace(
							replace(
							replace(
							replace(
							replace(
							replace(
							replace(
							replace(
							replace(@pFootprintValue COLLATE SQL_Latin1_General_CP1_CS_AS
								, '__' + char(66), '!')  --B
								, '__' + char(98), ' ')  --b
								, '__' + char(65), '+')  --A
								, '__f', '/')
								, '__' + char(117), '-') --u
								, '__4', '$')
								, '__' + char(71), '<')  --G
								, '__a', '''')
								, '__5', '%')
								, '__' + char(113), '"') --q
								, '__6', '^')
								, '__' + char(87), '{')  --W
								, '__t', '`')
								, '__7', '%')
								, '__' + char(119), '}') --w
								, '__' + char(109), '@') --m
								, '__8', '*')
								, '__' + char(67), '=')  --C
								, '__' + char(100), '.') --d
								, '__0', '~')
								, '__I', '|')
								, '__s', ';')
								, '__' + char(70), '\')  --F
								, '__' + char(77), ',')  --M
								, '__' + char(99), ':')  --c
								, '__' + char(81), '?')  --Q
								, '__' + char(112), ')') --p
								, '__' + char(101), ']') --e
								, '__' + char(80), '(')  --P
								, '__' + char(69), '[')  --E
								, '__3', '#')
								, '__' + char(103), '>') --g


		return @pMappedValue
	end

Advertisement