SQL Server User Defined Functions (UDF) for common data-conversion, parsing, comparing and formatting requirements
Throughout the much more substantial SQL-Server source-code examples that I have provided on this blog, you will see used, at various times, certain User Defined Functions (UDFs) and Stored Procedures (SPs) which make the code simpler than in-lining the same functionality. For performance reasons, there are many times where inlining this simpler code will make a lot of sense, but quite often it will be unnecessary — not to mention it will make your code base easier to maintain such code is more modular.I have tried arrange these helpful functions into some logical groupings within the Transact-SQL code provided (there may be multiple CREATE statements per code-block shown here).
SQL-Server User Defined Function (UDF) Source Code
Some DATETIME related ones...
It is an incredibly common requirement to need to convert between builtin SQL DATETIME values and Strings of YYYYMMDD characters. These functions will certainly help in that regard. Note: always check to see if the latest version of SQL-Server has finally included a simple built-in function for these tasks (it may eventually happen).--********************************************************************************
--This source code is Copyright (c) 2007-2017
-- Author: Mike Eberhart
--
--I hereby release this code under the terms of the MIT License (for freeware).
--
--Permission is hereby granted, free of charge, to any person obtaining a copy
--of this software and associated documentation files (the "Software"), to deal
--in the Software without restriction, including without limitation the rights
--to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
--copies of the Software, and to permit persons to whom the Software is
--furnished to do so, subject to the following conditions:
--
--The above copyright notice and this permission notice shall be included in
--all copies or substantial portions of the Software.
--
--THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
--IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
--FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
--AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
--LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
--OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
--THE SOFTWARE.
--********************************************************************************
--**********************************************************************************************
-- Convert a DATETIME into an integer that when displayed looks like a CHAR(8) String
-- in the format: YYYYMMDD
--
-- TESTS:
-- SELECT dbo.[udfConvertDatetimeToYYYYMMDDInteger](getdate())
--**********************************************************************************************
CREATE FUNCTION [udfConvertDatetimeToYYYYMMDDInteger]
(
@dDateTime DATETIME = NULL
)
RETURNS INTEGER
AS
BEGIN
RETURN ( DATEPART( yy, @dDateTime ) * 10000 )
+ ( DATEPART( mm, @dDateTime ) * 100 )
+ DATEPART( dd, @dDateTime )
END
--**********************************************************************************************
-- Convert a DATETIME and into a CHAR(8) String in the format: YYYYMMDD
-- This is an oft-needed function for date fields in tables where Dates are regularly involved
-- in ORDER BY and BETWEEN operations.
--**********************************************************************************************
CREATE FUNCTION [udfConvertDateToString]
(
@dDateTime DATETIME
)
RETURNS CHAR(8)
AS
BEGIN
RETURN CONVERT(CHAR(8), @dDateTime, 112);
END
Predictably Comparing NULL-ABLE (nullable) values...
A very common need is to easily and predictably compare two values where either or both may be nullable.These Transact-SQL (T-SQL) user-defined functions (UDF) will make the job easier. There is a routine that compares two Nullable Integer Values, and returns ONE (1) if the column values are the same (including NULL = NULL), otherwise returns ZERO. The next does the same for MONEY fields, i.e., compares two Nullable Money Values, and returns ONE (1) if the column values are the same (including NULL = NULL), otherwise returns ZERO. The following does this for STRING fields, i.e., Compare two Nullable String Values, and returns ONE (1) if the column values are the same (including NULL = NULL), otherwise returns ZERO.
As with any user defined functions, for performance reasons, consider equivalent inline code (in a set / batch operation) with functionality to perform such comparisons.
-**********************************************************************************************
-- Compare two Nullable Integer Values, and return ONE (1) if the column values are the same
-- (including NULL = NULL), otherwise returns ZERO.
--
-- SOME EXAMPLES / TEST CASES:
-- ===========================
--SET NOCOUNT ON
--SELECT 'TEST1: ' + CASE
-- WHEN dbo.[udfNullableIntegerCompare](NULL, NULL) = 1 THEN 'SUCCESS' ELSE 'FAIL' END;
--SELECT 'TEST2: ' + CASE
-- WHEN dbo.[udfNullableIntegerCompare](NULL, 1) = 0 THEN 'SUCCESS' ELSE 'FAIL' END;
--SELECT 'TEST3: ' + CASE
-- WHEN dbo.[udfNullableIntegerCompare](22, NULL) = 0 THEN 'SUCCESS' ELSE 'FAIL' END;
--SELECT 'TEST4: ' + CASE
-- WHEN dbo.[udfNullableIntegerCompare](22, 22) = 1 THEN 'SUCCESS' ELSE 'FAIL' END;
--SELECT 'TEST5: ' + CASE
-- WHEN dbo.[udfNullableIntegerCompare](15, 22) = 0 THEN 'SUCCESS' ELSE 'FAIL' END;
--**********************************************************************************************
CREATE FUNCTION [dbo].[udfNullableIntegerCompare]
( @Integer1 INT,
@Integer2 INT
)
RETURNS BIT
AS
BEGIN
RETURN
(
CASE
WHEN
(@Integer1 IS NULL AND @Integer2 IS NOT NULL) OR
(@Integer2 IS NULL AND @Integer1 IS NOT NULL)
THEN 0
ELSE
( 1 - ABS( SIGN( ISNULL( @Integer1, 0 ) - ISNULL( @Integer2, 0) ) ) )
END
);
END
--**********************************************************************************************
-- Compare two Nullable Money Values, and return ONE (1) if the column values are the same
-- (including NULL = NULL), otherwise returns ZERO.
--
-- SOME EXAMPLES / TEST CASES:
-- ===========================
--SET NOCOUNT ON
--SELECT 'TEST1: ' + CASE
-- WHEN dbo.[udfNullableMoneyCompare](NULL, NULL) = 1 THEN 'SUCCESS' ELSE 'FAIL' END;
--SELECT 'TEST2: ' + CASE
-- WHEN dbo.[udfNullableMoneyCompare](NULL, 1) = 0 THEN 'SUCCESS' ELSE 'FAIL' END;
--SELECT 'TEST3: ' + CASE
-- WHEN dbo.[udfNullableMoneyCompare](.12, NULL) = 0 THEN 'SUCCESS' ELSE 'FAIL' END;
--SELECT 'TEST4: ' + CASE
-- WHEN dbo.[udfNullableMoneyCompare](22.15, 22.15) = 1 THEN 'SUCCESS' ELSE 'FAIL' END;
--SELECT 'TEST5: ' + CASE
-- WHEN dbo.[udfNullableMoneyCompare](15.15, 22.22) = 0 THEN 'SUCCESS' ELSE 'FAIL' END;
--SELECT 'TEST6: ' + CASE
-- WHEN dbo.[udfNullableMoneyCompare](.15, .15) = 1 THEN 'SUCCESS' ELSE 'FAIL' END;
--SELECT 'TEST7: ' + CASE
-- WHEN dbo.[udfNullableMoneyCompare](.01, .02) = 0 THEN 'SUCCESS' ELSE 'FAIL' END;
--**********************************************************************************************
CREATE FUNCTION [udfNullableMoneyCompare]
( @Money1 MONEY,
@Money2 MONEY
)
RETURNS BIT
AS
BEGIN
RETURN
(
CASE
WHEN
(@Money1 IS NULL AND @Money2 IS NOT NULL) OR
(@Money2 IS NULL AND @Money1 IS NOT NULL)
THEN 0
ELSE
(1 - ABS( SIGN( ISNULL( @Money1, 0 ) - ISNULL( @Money2, 0) ) ) )
END
);
END
--**********************************************************************************************
-- Compare two Nullable String Values, and return a bit value of ONE (1) if the column values
-- are the same (including NULL = NULL), otherwise returns ZERO.
-- The @ComparePostTrim parm specifies whether the strings should be compared post-trimming
-- (of white-space) or not.
--
-- SOME EXAMPLES / TEST CASES:
-- ===========================
--SET NOCOUNT ON
--SELECT 'TEST1: ' + CASE
-- WHEN dbo.[udfNullableStringCompare](NULL, NULL, 0) = 1 THEN 'SUCCESS' ELSE 'FAIL' END;
--SELECT 'TEST2: ' + CASE
-- WHEN dbo.[udfNullableStringCompare](NULL, NULL, 1) = 1 THEN 'SUCCESS' ELSE 'FAIL' END;
--SELECT 'TEST3: ' + CASE
-- WHEN dbo.[udfNullableStringCompare](NULL, 'TEST', 0) = 0 THEN 'SUCCESS' ELSE 'FAIL' END;
--SELECT 'TEST4: ' + CASE
-- WHEN dbo.[udfNullableStringCompare]('', NULL, 0) = 0 THEN 'SUCCESS' ELSE 'FAIL' END;
--SELECT 'TEST5: ' + CASE
-- WHEN dbo.[udfNullableStringCompare]('', NULL, 1) = 0 THEN 'SUCCESS' ELSE 'FAIL' END;
--SELECT 'TEST6: ' + CASE
-- WHEN dbo.[udfNullableStringCompare]('', '', 0) = 1 THEN 'SUCCESS' ELSE 'FAIL' END;
--SELECT 'TEST7: ' + CASE
-- WHEN dbo.[udfNullableStringCompare]('', '', 1) = 1 THEN 'SUCCESS' ELSE 'FAIL' END;
--SELECT 'TEST8: ' + CASE
-- WHEN dbo.[udfNullableStringCompare]('', ' ', 0) = 0 THEN 'SUCCESS' ELSE 'FAIL' END;
--SELECT 'TEST9: ' + CASE
-- WHEN dbo.[udfNullableStringCompare]('', ' ', 1) = 1 THEN 'SUCCESS' ELSE 'FAIL' END;
--SELECT 'TEST10:' + CASE
-- WHEN dbo.[udfNullableStringCompare]('TEST', NULL, 0) = 0 THEN 'SUCCESS' ELSE 'FAIL' END;
--SELECT 'TEST11:' + CASE
-- WHEN dbo.[udfNullableStringCompare](NULL, 'TEST', 1) = 0 THEN 'SUCCESS' ELSE 'FAIL' END;
--SELECT 'TEST12:' + CASE
-- WHEN dbo.[udfNullableStringCompare]('TEST', NULL, 1) = 0 THEN 'SUCCESS' ELSE 'FAIL' END;
--SELECT 'TEST13:' + CASE
-- WHEN dbo.[udfNullableStringCompare]('TEST', 'TEST', 0) = 1 THEN 'SUCCESS' ELSE 'FAIL' END;
--SELECT 'TEST14:' + CASE
-- WHEN dbo.[udfNullableStringCompare](' TEST ', 'TEST', 0) = 0 THEN 'SUCCESS' ELSE 'FAIL' END;
--SELECT 'TEST15:' + CASE
-- WHEN dbo.[udfNullableStringCompare](' TEST ', 'TEST', 1) = 1 THEN 'SUCCESS' ELSE 'FAIL' END;
--SELECT 'TEST16:' + CASE
-- WHEN dbo.[udfNullableStringCompare](' TEST ', 'DIFF', 0) = 0 THEN 'SUCCESS' ELSE 'FAIL' END;
--**********************************************************************************************
CREATE FUNCTION [dbo].[udfNullableStringCompare]
( @String1 VARCHAR(MAX),
@String2 VARCHAR(MAX),
@ComparePostTrim BIT
)
RETURNS BIT
AS
BEGIN
IF @ComparePostTrim = 1
BEGIN
SET @String1 = [dbo].[udfTrim](@String1);
SET @String2 = [dbo].[udfTrim](@String2);
END;
RETURN
(
CASE
WHEN @String1 IS NULL AND @String2 IS NULL THEN 1
WHEN @String1 IS NULL AND @String2 IS NOT NULL THEN 0
WHEN @String2 IS NULL AND @String1 IS NOT NULL THEN 0
WHEN (DataLength(@String1) <> DataLength(@String2)) OR (@String1 <> @String2) THEN 0
ELSE 1
END
);
END
STRING Trim (Full Trim) and Converting an "empty" String to NULL...
Something that was needed prior to SQL-Server 2016, here is a Microsoft SQL-Server Transact-SQL (T-SQL) user-defined function (UDF) that will perform a FULL Trim() on a string, removing both leading (LTRIM) and trailing (RTRIM) whitespace in a single Trim() function much like Oracle and others. Microsoft SQL Server only provides left & right (LTRIM , RTRIM) functions natively, and this function simplifies life a bit since such whitespace removal is a common requirement when cleaning up user-provided input data prior to storing in your database.NOTE: For performance reasons, if you need to do full string-trim operations on a large number of data rows, whether in a set-based operation or on a row-by-row basis that is executed frequently or within a CURSOR or tight loop, you should consider moving this code inline into your SQL statement instead of calling this function. In-lining the LTRIM(RTRIM(@vStringToTrim)) functionality may save a noteworthy amount of CPU cycles when working with large row counts and transaction volumes.
I also included a simple, related UDF which makes use of the first, whereby it takes an empty string and converts it to a NULL. The interpretation of "empty" varies based on @ComparePostTrim parm which enables a string of just spaces to also be interpreted as empty if you so desire.
--**********************************************************************************************
-- Perform a FULL Trim on a string, removing both leading and trailing whitespace.
-- Microsoft SQL Server only provides left & right (LTRIM , RTRIM) functions natively.
--
-- NOTE: For performance reasons, if you need to do full string-trim operations on a large
-- number of rows, whether in a set-based operation or otherwise, you will probably want to
-- *inline* the core functionality into your SQL statement instead of calling this function.
--
-- SOME EXAMPLES / TEST CASES:
-- ===========================
--SET NOCOUNT ON
--SELECT 'TEST1 (NULL condition): ' + CASE
-- WHEN [dbo].[udfTrim](NULL) IS NULL THEN 'SUCCESS' ELSE 'FAIL' END;
--SELECT 'TEST2 (no padding to remove): ' + CASE
-- WHEN [dbo].[udfTrim]('0000000123') = '0000000123' THEN 'SUCCESS' ELSE 'FAIL' END;
--SELECT 'TEST3 (left padding to remove): ' + CASE
-- WHEN [dbo].[udfTrim](' ABC') = 'ABC' THEN 'SUCCESS' ELSE 'FAIL' END;
--SELECT 'TEST4 (right padding to remove): ' + CASE
-- WHEN [dbo].[udfTrim]('123 ') = '123' THEN 'SUCCESS' ELSE 'FAIL' END;
--SELECT 'TEST5 (left and right padding to remove): ' + CASE
-- WHEN [dbo].[udfTrim](' 123 456 789 ') = '123 456 789' THEN 'SUCCESS' ELSE 'FAIL' END;
--**********************************************************************************************
CREATE FUNCTION [dbo].[udfTrim]
(@vStringToTrim VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
RETURN LTRIM(RTRIM(@vStringToTrim));
END
--**********************************************************************************************
-- This simply takes an empty string and converts it to a NULL. The interpretation of what
-- qualifies as "empty" varies based on @ComparePostTrim parm, which by default will Trim any
-- blanks (i.e., white-space) from the string prior to testing for "empty".
-- This routine is especially handy when working with GUI applications that are sending empty
-- strings to a procedure, when, instead, storing a NULL in the database is truly desired.
--
-- SOME EXAMPLES / TEST CASES:
-- ===========================
--SET NOCOUNT ON
--SELECT 'TEST1: ' + CASE
-- WHEN dbo.[udfConvertEmptyStringToNull](NULL, 0) IS NULL THEN 'SUCCESS' ELSE 'FAIL' END
--SELECT 'TEST2: ' + CASE
-- WHEN dbo.[udfConvertEmptyStringToNull](NULL, 1) IS NULL THEN 'SUCCESS' ELSE 'FAIL' END
--SELECT 'TEST3: ' + CASE
-- WHEN dbo.[udfConvertEmptyStringToNull]('', 0) IS NULL THEN 'SUCCESS' ELSE 'FAIL' END
--SELECT 'TEST4: ' + CASE
-- WHEN dbo.[udfConvertEmptyStringToNull]('', 1) IS NULL THEN 'SUCCESS' ELSE 'FAIL' END
--SELECT 'TEST5: ' + CASE
-- WHEN dbo.[udfConvertEmptyStringToNull](' ', 0) = ' ' THEN 'SUCCESS' ELSE 'FAIL' END
--SELECT 'TEST6: ' + CASE
-- WHEN dbo.[udfConvertEmptyStringToNull](' ', 1) IS NULL THEN 'SUCCESS' ELSE 'FAIL' END
--SELECT 'TEST7: ' + CASE
-- WHEN dbo.[udfConvertEmptyStringToNull]('ABC', 0)= 'ABC' THEN 'SUCCESS' ELSE 'FAIL' END
--SELECT 'TEST8: ' + CASE
-- WHEN dbo.[udfConvertEmptyStringToNull]('AB ', 1)= 'AB ' THEN 'SUCCESS' ELSE 'FAIL' END
--**********************************************************************************************
CREATE FUNCTION [udfConvertEmptyStringToNull]
(
@vString VARCHAR(MAX),
@ComparePostTrim BIT = 1
)
RETURNS VARCHAR(MAX)
AS
BEGIN
RETURN
(
CASE
WHEN (@ComparePostTrim = 1 AND dbo.udfTrim(@vString) = '') THEN NULL
WHEN (DataLength(@vString) = 0) THEN NULL
ELSE @vString
END
)
END --Function
Obtaining Padded-String equivalents of Money and Integer field values...
Another group of user-defined function (UDFs) to return a String representation of a Money-value or Integer-value that will be "padded" with your chosen padding-character. This routine is quite helpful when you need to manipulate consistent fixed-length "money" or "integer" values.Some of my more complex and interesting set-based subtotal and running-subtotal example source-code (see SQL-Server Source Code throughout this Blog) make use of this code. One such example is my SQL-Server Parameterized ORDER BY without Dynamic-SQL — Example 1 query / procedure. Another is the (PENDING) example of a procedure to List Unique-Column-Definitions and Tables-Where-Used query.
--**********************************************************************************************
-- Return a String representation of a Money-value that has been "padded" with a desired
-- pad-character (quite often a ZERO or a SPACE) to a desired-length.
-- NOTE: NULL in any parms returns null, MAX resulting string size currently assumed to be 100,
-- and the money-results are simply rounded (two digits past decimal) if not already so,
-- using default "Style" for CONVERT statement. Use Style=1 for commas, Style=2 for 4-dec prec.
--
-- SOME EXAMPLES / TEST CASES:
-- ===========================
--SET NOCOUNT ON
--SELECT 'TEST1: ' + CASE
-- WHEN dbo.[udfGetMoneyAsPaddedString](NULL, 1,' ', 0) IS NULL
-- THEN 'SUCCESS' ELSE 'FAIL' END
--SELECT 'TEST2: ' + CASE
-- WHEN dbo.[udfGetMoneyAsPaddedString](1234, 12,'0', DEFAULT) = '000001234.00'
-- THEN 'SUCCESS' ELSE 'FAIL' END
--SELECT 'TEST3: ' + CASE
-- WHEN dbo.[udfGetMoneyAsPaddedString](1234.567, 12,'0', DEFAULT) = '000001234.57'
-- THEN 'SUCCESS' ELSE 'FAIL' END
--SELECT 'TEST4: ' + CASE
-- WHEN dbo.[udfGetMoneyAsPaddedString](123123.45, 12,' ', 1) = ' 123,123.45'
-- THEN 'SUCCESS' ELSE 'FAIL' END
--SELECT 'TEST5: ' + CASE
-- WHEN dbo.[udfGetMoneyAsPaddedString](0123.45678, 12,'0', 2) = '0000123.4568'
-- THEN 'SUCCESS' ELSE 'FAIL' END
--**********************************************************************************************
CREATE FUNCTION [udfGetMoneyAsPaddedString]
( @Money MONEY,
@DesiredLen TINYINT = 100,
@PadChar CHAR(1),
@MoneyStyle TINYINT = 0 --This is the 0, 1, 2 Style value SQL Server Money Converts
)
RETURNS VARCHAR(100)
AS
BEGIN
RETURN
(
CASE
WHEN
(@Money IS NULL) OR (@DesiredLen IS NULL) OR
(@PadChar IS NULL) OR (@MoneyStyle IS NULL)
THEN NULL
ELSE
RIGHT( REPLICATE(@PadChar, @DesiredLen) +
CONVERT(VARCHAR(100), @Money, @MoneyStyle), @DesiredLen)
END
)
END --Function
--**********************************************************************************************
-- Return a String representation of an Integer-value that has been "padded" with a desired
-- pad-character (quite often a ZERO or a SPACE) to a desired-length.
-- NOTE: NULL in any parms returns null, MAX resulting string size currently assumed to be 100.
--
-- SOME EXAMPLES / TEST CASES:
-- ===========================
--SET NOCOUNT ON
--SELECT 'TEST1: ' + CASE
-- WHEN dbo.[udfGetIntegerAsPaddedString](NULL, 1,'A') IS NULL
-- THEN 'SUCCESS' ELSE 'FAIL' END
--SELECT 'TEST2: ' + CASE
-- WHEN dbo.[udfGetIntegerAsPaddedString](123, 12,'0') = '000000000123'
-- THEN 'SUCCESS' ELSE 'FAIL' END
--**********************************************************************************************
CREATE FUNCTION [udfGetIntegerAsPaddedString]
( @Integer INT,
@DesiredLen TINYINT = 100,
@PadChar CHAR(1) = '0'
)
RETURNS VARCHAR(100)
AS
BEGIN
RETURN
(
CASE
WHEN
(@Integer IS NULL) OR (@DesiredLen IS NULL) OR (@PadChar IS NULL) THEN NULL
ELSE
RIGHT( REPLICATE(@PadChar, @DesiredLen) +
CONVERT(VARCHAR(100), @Integer), @DesiredLen)
END
)
END --Function
Misc: Convert a String to a Decimal (safely)...
A Microsoft SQL-Server Transact-SQL (T-SQL) user-defined function (UDF) to Convert a String (i.e., VARCHAR) value into a DECIMAL numeric type while preventing errors during the process by checking for properly-formatted decimal-value-equivalent-string prior to conversion. This covers a fairly broad range of conversion issues, and can be further enhanced to fit yoru particular needs.--**********************************************************************************************
-- Function to perform "SAFE" (non-error-producing) conversion of a string value to
-- a decimal number while setting the converted value to NULL if the conversion
-- does not "make sense."
--
-- TESTS:
--
--SELECT dbo.[udfConvertStringToDecimal]('123')
--SELECT dbo.[udfConvertStringToDecimal]('123.4567891234')
--SELECT dbo.[udfConvertStringToDecimal]('.') --0.0000
--SELECT dbo.[udfConvertStringToDecimal]('+') --0.0000
--SELECT dbo.[udfConvertStringToDecimal]('-') --0.0000
--SELECT dbo.[udfConvertStringToDecimal]('+.') --0.0000
--SELECT dbo.[udfConvertStringToDecimal]('abc.123') --NULL
--SELECT dbo.[udfConvertStringToDecimal]('1.2.3') --NULL
--SELECT dbo.[udfConvertStringToDecimal]('-123-234') --NULL
--SELECT dbo.[udfConvertStringToDecimal]('+2.+34') --NULL
--**********************************************************************************************
CREATE FUNCTION [udfConvertStringToDecimal]
(
@sValueToConvert VARCHAR( 255 ) = NULL
)
RETURNS DECIMAL( 17, 7 )
AS
BEGIN
SELECT @sValueToConvert = dbo.udfTrim(@sValueToConvert)
DECLARE @valLen SMALLINT = LEN(@sValueToConvert)
-- Check that only characters within the set -+.0123456789 exist in @@sValueToConvert.
-- Next, if ONLY VALID CHARACTERS EXIST, if any are unary operators (+/-), make sure only appear as first char.
-- Next, if a decimal appears, make sure only ONE of them.
IF PATINDEX('%[^-+.0-9]%', @sValueToConvert) = 0
IF CHARINDEX('-', REVERSE(@sValueToConvert)) IN ( 0, @valLen ) --unary - operator only allwed as first-char in string
IF CHARINDEX('+', REVERSE(@sValueToConvert)) IN ( 0, @valLen ) --unary + ...
IF PATINDEX('%.%.%', @sValueToConvert) = 0 --only ONE decimal char allowed
IF PATINDEX('%[-+.]%', RIGHT(@sValueToConvert,1)) = 1 --handle single-char-string of only a special-char
RETURN CONVERT( DECIMAL( 17, 7), CONVERT(FLOAT, @sValueToConvert + '0') )
ELSE
RETURN CONVERT( DECIMAL( 17, 7), CONVERT(FLOAT, @sValueToConvert) )
RETURN NULL
END
Misc: USA Social-Security and Phone-Number formatting...
Here is a Microsoft SQL-Server Transact-SQL (T-SQL) user-defined function (UDF) that converts unformatted 9-digit Social-Security Numbers (i.e., no embedded hyphens) to standard "xxx-xx-xxxx" SSN display format commonly seen on reports and GUIs.Next, there is a UDF that will convert an unformatted 10-character telephone number (i.e., full USA 10-digit phone number including area-code, but without any parentheses surrounding area-code, dashes separating numbers, spaces, or other junk: simply a 10-character-long string representing the phone#), to a "(xxx) xxx-xxx" formatted layout.
E.g., converts the string / character telephone number value in format of '1234567890' into a return formatted phone-number value of '(123) 456-7890'. Keep in mind, since we are considering a telephone number to be character data, you can use this for "alphanumeric phone numbers" like: 800SQLCODE --> (800) SQL CODE also.
As with any user defined functions, for performance reason, if converting a large number of SSNs or phone numbers (in a set / batch operation), you may wish to inline the functionality of this source-code into the SQL performing those conversions.
/***************************************************************************************/
--This function will convert unformatted Social-Security Numbers (i.e., SSN numbers with
--no embedded hyphens) to standard "xxx-xx-xxxx" USA SSN display format.
--
--PARAMTER:
-- @cSSN: simply a 9-character-long string representing the SSN#.
-- This routine ASSUMES all 9-chars are filled in at the start (fixed-length, 9 chars)!
--
--RETURNS: formatted "xxx-xx-xxxx" social security number in common display layout / style.
--
--EXAMPLE / TEST:
-- PRINT dbo.udfConvertUnformattedSSNToFormattedSSN('123456789') --returns '123-45-6789'
/***************************************************************************************/
CREATE FUNCTION [dbo].[udfConvertUnformattedSSNToFormattedSSN]
(@cSSN CHAR(9))
RETURNS CHAR(11)
AS
BEGIN
RETURN SUBSTRING(@cSSN, 1, 3) + '-' + SUBSTRING(@cSSN, 4, 2) + '-' + SUBSTRING(@cSSN, 6,4);
END
/***************************************************************************************/
--This function convert an unformatted 10-character telephone number to a formatted one.
--
--PARAMTER:
-- @cPhone: simply a 10-character-long string representing the phone#.
-- This routine ASSUMES all 10-chars are filled in at the start!
-- Input phone# is a USA 10-digit phone number including area-code, but without any
-- parentheses surrounding area-code, dashes separating numbers, spaces, or other junk.
--
--RETURNS: formatted "(xxx) xxx-xxxx" phone number in a common display layout / style.
--
--EXAMPLE / TEST:
-- SELECT [dbo].[udfFormatUnformattedPhone]('1234567890') --will return '(123) 456-7890'
/***************************************************************************************/
CREATE FUNCTION [dbo].[udfFormatUnformattedPhone]
(@cPhone CHAR(10))
RETURNS CHAR(14)
AS
BEGIN
RETURN '(' +
SUBSTRING(@cPhone, 1, 3) + ') ' +
SUBSTRING(@cPhone, 4, 3) + '-' +
SUBSTRING(@cPhone, 7, 4);
END
Continue to read this Software Development and Technology Blog for computer programming, software development, and technology Techniques, How-To's, Fixes, Reviews, and News — focused on Dart Language, SQL Server, Nvidia CUDA, VMware, TypeScript, SVG, other technology tips and how-to's, and my varied political and economic opinions.
No comments:
Post a Comment