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!
