Left and Right-Padding
Oracle has the LPAD and RPAD functions which return and expression, left-padded, respectively right-padded with the specified characters. The functions are useful in formatting unique identifiers to fit a certain format (eg. 0000012345 instead of 12345). Here’re similar implementations of the respective functions:
-- left padding function CREATE FUNCTION dbo.LeftPadding( @str varchar(50) , @length int , @padchar varchar(1)) RETURNS varchar(50) AS BEGIN RETURN CASE WHEN LEN(@str)<@length THEN Replicate(@padchar, @length-LEN(@str)) + @str ELSE @str END END -- example left padding SELECT dbo.LeftPadding('12345', '10', '0') -- right padding function CREATE FUNCTION dbo.RightPadding( @str varchar(50) , @length int , @padchar varchar(1)) RETURNS varchar(50) AS BEGIN RETURN CASE WHEN LEN(@str)<@length THEN @str + Replicate(@padchar, @length-LEN(@str)) ELSE @str END END -- example right padding SELECT dbo.RightPadding('12345', '10', '0')
Left and Right Side
When multiple pieces of data are stored within same same attribute, it’s helpful to get the left, respectively the right part of the string based on a given delimiter, where the reverse flag tells the directions in which the left is applied:
-- left part function CREATE FUNCTION dbo.CutLeft( @str varchar(max) , @delimiter varchar(1) , @reverse bit = 0) RETURNS varchar(max) AS BEGIN RETURN CASE WHEN CharIndex(@delimiter, @str)>0 THEN CASE @reverse WHEN 0 THEN Left(@str, CharIndex(@delimiter, @str)-1) ELSE Left(@str, Len(@str)-CharIndex(@delimiter, Reverse(@str))) END ELSE @str END END -- example left part SELECT dbo.CutLeft('12345,045,000', ',', 0) , dbo.CutLeft('12345,045,000', ',', 1) -- right part function CREATE FUNCTION dbo.CutRight( @str varchar(max) , @delimiter varchar(1) , @reverse bit = 0) RETURNS varchar(max) AS BEGIN RETURN CASE WHEN CharIndex(@delimiter, @str)>0 THEN CASE @reverse WHEN 0 THEN Right(@str, CharIndex(@delimiter, Reverse(@str))-1) ELSE Right(@str, Len(@str)-CharIndex(@delimiter, @str)) END ELSE @str END END -- example right part SELECT dbo.CutRight('12345,045,000', ',', 0) , dbo.CutRight('12345,045,000', ',', 1)
Replacing Special Characters
Special characters can prove to be undesirable in certain scenarios (e.g. matching values, searching). Until a “Replace from” function will be made available, the solution is to include the replacements in a user-defined function similar with the one below:
DROP FUNCTION IF EXISTS [dbo].[ReplaceSpecialChars] CREATE FUNCTION [dbo].[ReplaceSpecialChars]( @string nvarchar(max) , @replacer as nvarchar(1) = '-' ) RETURNS nvarchar(max) -- replaces special characters with a given character (e.g. an empty string, space) AS BEGIN IF CharIndex('*', @string) > 0 SET @string = replace(@string, '*', @replacer) IF CharIndex('#', @string) > 0 SET @string = replace(@string, '#', @replacer) IF CharIndex('$', @string) > 0 SET @string = replace(@string, '$', @replacer) IF CharIndex('%', @string) > 0 SET @string = replace(@string, '%', @replacer) IF CharIndex('&', @string) > 0 SET @string = replace(@string, '&', @replacer) IF CharIndex(';', @string) > 0 SET @string = replace(@string, ';', @replacer) IF CharIndex('/', @string) > 0 SET @string = replace(@string, '/', @replacer) IF CharIndex('?', @string) > 0 SET @string = replace(@string, '?', @replacer) IF CharIndex('\', @string) > 0 SET @string = replace(@string, '\', @replacer) IF CharIndex('(', @string) > 0 SET @string = replace(@string, '(', @replacer) IF CharIndex(')', @string) > 0 SET @string = replace(@string, ')', @replacer) IF CharIndex('|', @string) > 0 SET @string = replace(@string, '|', @replacer) IF CharIndex('{', @string) > 0 SET @string = replace(@string, '{', @replacer) IF CharIndex('}', @string) > 0 SET @string = replace(@string, '}', @replacer) IF CharIndex('[', @string) > 0 SET @string = replace(@string, '[', @replacer) IF CharIndex(']', @string) > 0 SET @string = replace(@string, ']', @replacer) RETURN (LTrim(RTrim(@string))) END SELECT [dbo].[ReplaceSpecialChars]('1*2#3$4%5&6;7/8?9\10(11)12|13{14}15[16]', '') SELECT [dbo].[ReplaceSpecialChars]('1*2#3$4%5&6;7/8?9\10(11)12|13{14}15[16]', ' ')
Other type of special characters are the umlauts (e.g. ä, ß, ö, ü from German language):
DROP FUNCTION IF EXISTS [dbo].[ReplaceUmlauts] CREATE FUNCTION [dbo].[ReplaceUmlauts]( @string nvarchar(max) ) RETURNS nvarchar(max) -- replaces umlauts with their equivalent AS BEGIN IF CharIndex('ä', @string) > 0 SET @string = replace(@string, 'ä', 'ae') IF CharIndex('ö', @string) > 0 SET @string = replace(@string, 'ö', 'oe') IF CharIndex('ß', @string) > 0 SET @string = replace(@string, 'ß', 'ss') IF CharIndex('%', @string) > 0 SET @string = replace(@string, 'ü', 'ue') RETURN Trim(@string) END SELECT [dbo].[ReplaceUmlauts]('Hr Schrötter trinkt ein heißes Getränk')
Handling Umlauts
Another type of specials characters are the letter specific to certain languages that deviate from the Latin characters (e.g. umlauts in German, accents in French), the usage of such characters introducing further challenges in handling the characters, especially when converting the data between characters sets. A common scenario is the one in which umlauts in ISO-8891-1 are encoded using two character sets. Probably the easiest way to handle such characters is to write a function as follows:
-- create the function CREATE FUNCTION [dbo].[ReplaceCodes2Umlauts]( @string nvarchar(max) ) RETURNS nvarchar(max) -- replaces ISO 8859-1 characters with the corresponding encoding AS BEGIN IF CharIndex('ß', @string) > 0 SET @string = replace(@string, 'ß', 'ß') IF CharIndex('ö', @string) > 0 SET @string = replace(@string, 'ö', 'ö') IF CharIndex('Ö', @string) > 0 SET @string = replace(@string, 'Ö', 'Ö') IF CharIndex('ü', @string) > 0 SET @string = replace(@string, 'ü', 'ü') IF CharIndex('Ãœ', @string) > 0 SET @string = replace(@string, 'Ãœ', 'Ü') IF CharIndex('ä', @string) > 0 SET @string = replace(@string, 'ä', 'ä') IF CharIndex('Ä', @string) > 0 SET @string = replace(@string, 'Ä', 'Ä') RETURN (LTrim(RTrim(@string))) END --test the function SELECT [dbo].[ReplaceCodes2Umlauts]('Falsches Ãœben von Xylophonmusik quält jeden größeren Zwerg')
In the inverse scenario, at least for the German language is possible to replace the umlauts with a set the corresponding transliterations:
-- drop the function DROP FUNCTION IF EXISTS [dbo].[ReplaceUmlauts] -- create the function CREATE FUNCTION [dbo].[ReplaceUmlauts]( @string nvarchar(max) ) RETURNS nvarchar(max) -- replaces umlauts with corresponding transliterations AS BEGIN IF CharIndex('ß', @string) > 0 SET @string = replace(@string, 'ß', 'ss') IF CharIndex('ü', @string) > 0 SET @string = replace(@string, 'ü', 'ue') IF CharIndex('ä', @string) > 0 SET @string = replace(@string, 'ä', 'ae') IF CharIndex('ö', @string) > 0 SET @string = replace(@string, 'ö', 'oe') RETURN (LTrim(RTrim(@string))) END --test the function SELECT [dbo].[ReplaceUmlauts]('Falsches üben von Xylophonmusik quält jeden größeren Zwerg')
Happy coding!
No comments:
Post a Comment