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

Question

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.

Should be able to adjust one of the answers here to do the transformation via TSQL http://stackoverflow.com/q/230138/73226 but really you would need to do this in whatever your presentation layer language is as you can't alter the column names sent back without using dynamic SQL. — Martin Smith, Aug 30 '11 at 14:36
@Smith, Each of the column names are saved in one of the master tables. I'm generating the UI from the master table columns not from the columns directly. — Nagesh, Aug 30 '11 at 14:50
What are you using to generate the UI? The string functions in TSQL are limited. Does you development environment support regular expressions. The As alias has to be hard coded so you cannot dynamically change the column name returned by TSQL. — paparazzo, Aug 30 '11 at 15:15

EricZ · Accepted Answer · 2011-08-31T13:22:50.337

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

There is a problem, if the InputString has spaces between characters the function hangs. Please correct it. — Nagesh, Aug 31 '11 at 04:17
I changed the function. It should be able to handle space now. — EricZ, Aug 31 '11 at 13:23

score 0 · Answer 2 · answered Aug 30 '11 at 15:19

0

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.

answered Aug 30 '11 at 15:19

Praveen

61
3

score 0 · Answer 3 · edited May 23 '17 at 12:19

0

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

edited May 23 '17 at 12:19

Community

1
1

answered Aug 30 '11 at 15:37

David Steele

3,433
21
23

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

3 Answers3