USE PatientFirstIntranet ;
GO
CREATE FUNCTION dbo.svfProperCaseJobTitles
(@Text VARCHAR(8000))
RETURNS VARCHAR(8000)
AS
BEGIN
	DECLARE
	@OrigTextString VARCHAR(8000)
	,@TextStringToProcess VARCHAR(8000)
	,@TextStringToAppend VARCHAR(10)
	,@Reset BIT
	,@Ret VARCHAR(8000)
	,@i INT
	,@c CHAR(1)
	;
	SELECT
	@OrigTextString = @Text
	,@Reset = 1
	,@i = 1
	,@Ret = ''
	;
	SELECT
	@TextStringToProcess =
		CASE LEN(@OrigTextString)
			WHEN 2 THEN ''
			WHEN 3 THEN ''
			ELSE
				CASE
					WHEN SUBSTRING(RTRIM(@OrigTextString),LEN(RTRIM(@OrigTextString)) - 3,4) = ' III' THEN LEFT(RTRIM(@OrigTextString),LEN(RTRIM(@OrigTextString)) - 4)
					WHEN SUBSTRING(RTRIM(@OrigTextString),LEN(RTRIM(@OrigTextString)) - 2,3) IN (' II',' IV') THEN LEFT(RTRIM(@OrigTextString),LEN(RTRIM(@OrigTextString)) - 3)
					WHEN SUBSTRING(RTRIM(@OrigTextString),LEN(RTRIM(@OrigTextString)) - 2,3) IN ('-RN','-RE') THEN LEFT(RTRIM(@OrigTextString),LEN(RTRIM(@OrigTextString)) - 3)
					ELSE @OrigTextString
				END
		END
	,@TextStringToAppend =
		CASE LEN(@OrigTextString)
			WHEN 2 THEN RTRIM(@OrigTextString)
			WHEN 3 THEN RTRIM(@OrigTextString)
			ELSE
				CASE
					WHEN SUBSTRING(RTRIM(@OrigTextString),LEN(RTRIM(@OrigTextString)) - 3,4) = ' III' THEN RIGHT(RTRIM(@OrigTextString),4)
					WHEN SUBSTRING(RTRIM(@OrigTextString),LEN(RTRIM(@OrigTextString)) - 2,3) IN (' II',' IV') THEN RIGHT(RTRIM(@OrigTextString),3)
					WHEN SUBSTRING(RTRIM(@OrigTextString),LEN(RTRIM(@OrigTextString)) - 2,3) IN ('-RN','-RE') THEN RIGHT(RTRIM(@OrigTextString),3)
					ELSE ''
				END
		END
	;
	WHILE (@i <= LEN(@TextStringToProcess))
		SELECT
		@c= SUBSTRING(@TextStringToProcess,@i,1)
		,@Ret =
			CASE
				WHEN @i = '3' THEN
					CASE
						WHEN @Ret = 'PC ' THEN 'PC'
						ELSE @Ret
					END
				ELSE @Ret
			END
			+
			CASE
				WHEN @Reset = 1 THEN UPPER(@c)
				ELSE LOWER(@c)
			END
		,@Reset =
			CASE
				
				WHEN @c LIKE '[a-zA-Z]' THEN 0
				ELSE 1
			END
		,@i = @i + 1
	RETURN @Ret + @TextStringToAppend
END
;