SQL to format strings in a conditional manner like inserting spaces between big (capital) and small letters

I'm generating the user interface labels from database columns in one of my applications. While doing this I came across an issue. The same column names as in my database table 开发者_开发技巧is being shown in the UI which I wish to change.

I have sample data like this:

ProductionIssueID
MAXSerialNo
ProductID
Item

I wish to format them like this:

Production Issue ID
MAX Serial No
Product ID
Item

The logic is:

1. When a capital alphabet succeeds the small alphabet then a space has to be inserted as shown in Production Issue ID

2. When a small alphabet succeeds a capital one then space has to be inserted as given in MAX Serial No and Product ID

3. If there is Only one capital alphabet nothing has to be done as in Item

4. No space is required between a similar pair of capital/small alphabets.

Please help me to achieve this. The examples are more descriptive.

Thanks in advance for your valuable time and efforts.

I agree this should be done on presentation layer. However, this function can get what you need.

CREATE FUNCTION [dbo].[fn_InitCapWord] ( @InputString varchar(1000) ) 
RETURNS VARCHAR(1000)
AS
BEGIN

DECLARE @Index          INT
DECLARE @OutputString   VARCHAR(1000)
DECLARE @substr  VARCHAR(1000)

SET @OutputString = ''

WHILE (LEN(@InputString) > 0)
BEGIN
    IF (SUBSTRING(@InputString ,2,1) COLLATE Latin1_General_BIN LIKE '%[A-Z]%')
            SELECT @Index = PATINDEX('%[a-z]%',SUBSTRING(@InputString,2,999) COLLATE Latin1_General_BIN) -1
    ELSE
            SELECT @Index = PATINDEX('%[A-Z]%',SUBSTRING(@InputString,2,999) COLLATE Latin1_General_BIN)  

    SELECT @substr = 
        CASE WHEN @Index >= 1 
            THEN  SUBSTRING(@InputString,1,@Index)
            ELSE  @InputString
        END                     
    SELECT @OutputString = @OutputString + RTRIM(@substr) + ' '
    SELECT @InputString = RIGHT(@InputString,CASE WHEN @Index<=0 THEN 0 ELSE LEN(@InputString) - @Index END)
END 

RETURN RTRIM(@OutputString)
END

Example:

PRINT dbo.fn_InitCapWord('ProductionIssueID')
PRINT dbo.fn_InitCapWord('MAXSerialNo')
PRINT dbo.fn_InitCapWord('ProductID')
PRINT dbo.fn_InitCapWord('Item')

OUTPUT:

Production Issue ID
MAX Serial No
Product ID
Item

UPDATE: I changed the function. It should be able to handle space now.

PRINT dbo.fn_InitCapWord('Production IssueID')

OUTPUT:

Production Issue ID

SQL is meant/good at handling the relational data. It's always a good practice to do the string manipulation on the presentation side. If you still want you can create a CLR function and use it.

I agree this is purely a presentation issue and is best done in the presentation layer. There is a previous question here that gives you a few suggestions (both with and without regex)

add-spaces-before-capital-letters