MYOB Exo – Useful SQL Server Scalar Functions

The following are a collection of User Defined Functions I’ve found variously on the Internet. I use these extensively now to do simple data transforms when pulling reports, such as converting all line breaks to pipe characters so that copy/paste into Excel works nicely.

Behind the cut I’ve got several functions, and they’re all wrapped in handy dynamic scripts to install them easily.

• UDF_GetNumeric – which pulls all numbers from a string. (I use it for parsing account numbers from text strings)
• UDF_LineCleaner – which removes characters that can’t be used in Windows Filenames from character strings, useful for folder/file manipulation.
• UDF_StandardLineBreaks – Useful if you’re having problems with line breaks not always being line breaks. Hunts for and fixes all line breaks into being the standard windows format. (Useful if getting data from Macs or Linux machines)
• UDF_LineBreaksToPipes – Useful for copy/pasting from SQL management studio into Excel or bad CSV formats.

UDF GetNumeric

IF NOT EXISTS (select 1 from sys.objects where name like 'udf_GetNumeric' AND TYPE='FN')
	BEGIN
	EXEC ('
CREATE FUNCTION [dbo].[udf_GetNumeric]
(@strAlphaNumeric VARCHAR(256))
RETURNS VARCHAR(256)
--Function added by Will Howard to support BANKFEEDS rules.  Used in [dbo].[FN_BANKFEEDS_BANKTRANSACTIONS]  to give good data to [dbo].[FN_BANKFEEDS_EXTRACT_DEBTORCODE] .
--Function came from StackOverflow - http://stackoverflow.com/questions/16667251/query-to-get-only-numbers-from-a-string
AS
BEGIN
DECLARE @intAlpha INT
SET @intAlpha = PATINDEX(''%[^0-9]%'', @strAlphaNumeric)
BEGIN
WHILE @intAlpha > 0
BEGIN
SET @strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha, 1, '''' )
SET @intAlpha = PATINDEX(''%[^0-9]%'', @strAlphaNumeric )
END
END
RETURN ISNULL(@strAlphaNumeric,0)
END
	')
	END

UDF_LineCleaner

IF NOT EXISTS (select 1 from sys.objects where name like 'udf_LineCleaner' AND TYPE='FN')
	BEGIN
	EXEC ('CREATE FUNCTION [dbo].[udf_LineCleaner]
(@strAlphaNumeric VARCHAR(1000))
RETURNS VARCHAR(256)
--Function added by Will Howard to support make coding easier for line information.
AS
BEGIN
	Select @strAlphaNumeric=LTRIM(RTRIM(REPLACE(REPLACE(@strAlphaNumeric,CHAR(13),''''),CHAR(10),'''')))
	RETURN ISNULL(@strAlphaNumeric,'''')
END')
	END

UDF_StandardLineBreaks

IF NOT EXISTS (select 1 from sys.objects where name like 'udf_StandardLineBreaks' AND TYPE='FN')
	BEGIN
	EXEC ('
CREATE FUNCTION [dbo].[udf_StandardLineBreaks]
(@strAlphaNumeric VARCHAR(5000))
RETURNS VARCHAR(256)
--Function added by Will Howard to support make coding easier for line information.
AS
BEGIN
	Select @strAlphaNumeric=
	LTRIM(RTRIM(
		REPLACE(
			REPLACE(
				REPLACE(
					REPLACE(
						REPLACE(@strAlphaNumeric,CHAR(10)+CHAR(13),''|'')
					,CHAR(13)+CHAR(10),''|'')
				,CHAR(13),''|'')
			,CHAR(10),''|'')
		,''|'',CHAR(13)+CHAR(10))
	))
	RETURN ISNULL(@strAlphaNumeric,'''')
END
	')
	END

UDF_LineBreaksToPipes

IF NOT EXISTS (select 1 from sys.objects where name like 'udf_LineBreaksToPipes' AND TYPE='FN')
	BEGIN
	EXEC ('
CREATE FUNCTION [dbo].[udf_LineBreaksToPipes]
(@strAlphaNumeric VARCHAR(5000))
RETURNS VARCHAR(256)
--Function added by Will Howard to support make coding easier for line information.
AS
BEGIN
	Select @strAlphaNumeric=LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(@strAlphaNumeric
	,CHAR(10)+CHAR(13),''|'')
	,CHAR(13)+CHAR(10),''|'')
	,CHAR(13),''|''),CHAR(10),''|'')))
	RETURN ISNULL(@strAlphaNumeric,'''')
END
	')END