× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



I found a handy SQL function to transform a string into proper case,
But I would like certain Acronyms be kept in upper case...

can someone share a example?


below is the function I have (I think I found it from IBMiSystems magazine)

CREATE FUNCTION Proper
(@tcString VARCHAR(100))
RETURNS VARCHAR(100)
Language SQL
Deterministic
Returns Null On Null Input
Set Option Commit=*None
Begin

/* Scratch variables used for processing */
DECLARE @outputString VARCHAR(100) Not Null Default '';
DECLARE @stringLength INT Not Null Default 0;
DECLARE @loopCounter INT Not Null Default 1;
DECLARE @charAtPos CHAR(1) Not Null Default '';
DECLARE @wordStart INT Not Null Default 1;

-- If the incoming string is NULL, return an error
--IF (@tcString IS NULL) Then
-- RETURN (null)

-- Initialize the scratch variables
SET @stringLength = LENGTH(@tcString);
--SET @loopCounter = 1;
--SET @wordStart = 1;

-- Loop over the string
WHILE (@loopCounter <= @stringLength) DO
-- Get the single character off the string
SET @charAtPos = LOWER(SUBSTR(@tcString, @loopCounter, 1));

-- If we are the start of a word, uppercase the character
-- and reset the word indicator
If (@wordStart = 1) Then
SET @charAtPos = UPPER(@charAtPos);
SET @wordStart = 0;
End If;

-- If we encounter a white space, indicate that we
-- are about to start a word
IF (@charAtPos = ' ') Then
SET @wordStart = 1;
End If;

-- Form the output string
SET @outputString = @outputString || @charAtPos;

SET @loopCounter = @loopCounter + 1;
END WHILE;

-- Return the final output
RETURN @outputString;

END

As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.