Convert proper case to space after proper case - SQL Server

Question

We have huge list of attributes in proper case and we would like to get output with proper space after each word

For example -

As-Is: ServiceProviderReferenceNumber

To-Be: Service Provider Reference Number

Any ideas on how I can convert this in T-SQL ? Any help would be appreciated

Note: We are using SQL Server 2016

Answer 1

    ;WITH a(s) AS 
    (
     SELECT 'ServiceProviderReferenceNumber' UNION 
     SELECT 'ConvertProperCaseToSpaceAfterProperCase' 
    )
    SELECT LTRIM(b.ss) FROM a
    CROSS APPLY (
        SELECT CASE WHEN ASCII(SUBSTRING(a.s,sv.number,1))  BETWEEN 65 AND 90 THEN ' '+SUBSTRING(a.s,sv.number,1) ELSE SUBSTRING(a.s,sv.number,1) END 
        FROM master.dbo.spt_values AS sv 
        WHERE sv.type='P' AND sv.number BETWEEN 1 AND LEN(a.s)
        FOR XML PATH('')

    ) b(ss)

Convert Proper Case To Space After Proper Case
Service Provider Reference Number

Answer 2

try this superfast recursive CTE

;with
w as (
    select 
        id, 
        cast(YourColumn collate LATIN1_GENERAL_BIN as varchar(500)) ss 
    from YourTable w
),
r as (
    select id, ss s, 2 l, PATINDEX('%[A-Z]%', ss) p, 1 i
    from w

    union all
    select w.id, cast(stuff(s, p, 0, ' ') as varchar(500)), p + 1 l, pos + p + 1 p, pos i
    from w
    join (
        select id, s, PATINDEX('%[A-Z]%', substring(s, p+1, 500) ) pos, p, i
        from r
    ) rr on w.id = rr.id and i>0
)
select id, s
from r
where i=0
OPTION (MAXRECURSION 500); 

Answer 3

This is an approach without a loop...

Besides the fact, that loops are something to avoid, this makes the handling of a full table really easy.

First I create a mock-up table with three strings

DECLARE @tbl TABLE(ID INT IDENTITY,YourString VARCHAR(100));
INSERT INTO @tbl VALUES('ServiceProviderReferenceNumber'),('SomeOther'),('AndOneMore');

The query will first create a numbers tally table on the fly, then split the string in single chars, check for capitalized letters and add a blank.

Finally this is reconcatenated and trimmed

Attention If you might need more than 1000 characters, just add one more ,Numbers AS c to the Tally (providing 10.000 numbers then)

WITH Numbers AS
 (SELECT Nr FROM(SELECT Nr FROM(VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS x(Nr)) AS y)
,Tally AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS Nr
    FROM Numbers,Numbers AS a,Numbers AS b
)
,Splitted AS
(
    SELECT ID 
          ,CASE WHEN ASCII(Chars.OneChar) BETWEEN ASCII('A') AND ASCII('Z') THEN ' ' + Chars.OneChar ELSE Chars.OneChar END AS TheChar
    FROM @tbl AS t
    CROSS APPLY (SELECT TOP(LEN(t.YourString)) Tally.Nr FROM Tally) AS Nmbr
    CROSS APPLY (SELECT SUBSTRING(t.YourString,Nmbr.Nr,1) AS OneChar) AS Chars
)
SELECT ID
      ,LTRIM((
        SELECT s.TheChar AS [*]
        FROM Splitted AS s
        WHERE s.ID=Splitted.ID
        FOR XML PATH('')
       )) AS ReConcatenated
FROM Splitted
GROUP BY ID

The result

1   Service Provider Reference Number
2   Some Other
3   And One More

UPDATE Comparison of ad-hoc vs. loop

CREATE DATABASE testDB;
GO

USE testDB;
GO

CREATE TABLE tbl(ID INT IDENTITY,YourString VARCHAR(100));
GO

CREATE FUNCTION dbo.TestF(@String VARCHAR(4000))
RETURNS VARCHAR(4000)
AS
BEGIN
    DECLARE @StringNew nvarchar(100) = '';
    DECLARE @Char nvarchar(1);
    DECLARE @len int = LEN(@String);
    DECLARE @i int = 0;

    WHILE @i <= @len
      BEGIN
        SET @i = @i+1;
        SET @Char = substring(@String,@i,1);

        IF (UNICODE(@Char) = UNICODE(UPPER(@Char)) AND @i > 1)
          SET @StringNew = @StringNew + ' ' + @Char;
        ELSE
          SET @StringNew = @StringNew + @Char;
      END;

      RETURN @StringNew
    END
GO

INSERT INTO tbl VALUES('ServiceProviderReferenceNumber'),('SomeOther'),('AndOneMore');
GO 100000

DECLARE @d DATETIME=GETDATE();

WITH Numbers AS
 (SELECT Nr FROM(SELECT Nr FROM(VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS x(Nr)) AS y)
,Tally AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS Nr
    FROM Numbers,Numbers AS a,Numbers AS b
)
,Splitted AS
(
    SELECT ID 
          ,CASE WHEN ASCII(Chars.OneChar) BETWEEN ASCII('A') AND ASCII('Z') THEN ' ' + Chars.OneChar ELSE Chars.OneChar END AS TheChar
    FROM tbl AS t
    CROSS APPLY (SELECT TOP(LEN(t.YourString)) Tally.Nr FROM Tally) AS Nmbr
    CROSS APPLY (SELECT SUBSTRING(t.YourString,Nmbr.Nr,1) AS OneChar) AS Chars
)
SELECT ID
      ,LTRIM((
        SELECT s.TheChar AS [*]
        FROM Splitted AS s
        WHERE s.ID=Splitted.ID
        FOR XML PATH('')
       )) AS ReConcatenated
FROM Splitted
GROUP BY ID;

SELECT CAST(GETDATE()-@d AS TIME);
GO

DECLARE @d DATETIME=GETDATE();

SELECT ID,dbo.TestF(tbl.YourString) AS ReConcatenated
FROM tbl

SELECT CAST(GETDATE()-@d AS TIME);
GO
USE master;
GO
DROP DATABASE testDB;

The result

**ad-hoc** 2.66 Seconds
**loop**   5.33

UPDATE 2: This answer by @sai bharath is faster...

The result

**ad-hoc**              2.66 Seconds
**loop**                5.33
**while with `STUFF`**  1.71

Answer 4

Here an approach, which loops through the string and compares the Uncicode Values of characters. I chose to compare the numbers instead of the characters, because depending on the DB configuration 'a'='A' evaluates as true.

DECLARE @String nvarchar(100) = 'ServiceProviderReferenceNumber';
DECLARE @StringNew nvarchar(100) = '';
DECLARE @Char nvarchar(1);
DECLARE @len int = LEN(@String);
DECLARE @i int = 0;

WHILE @i <= @len
  BEGIN
    SET @i = @i+1;
    SET @Char = substring(@String,@i,1);

    IF (UNICODE(@Char) = UNICODE(UPPER(@Char)) AND @i > 1)
      SET @StringNew = @StringNew + ' ' + @Char;
    ELSE
      SET @StringNew = @StringNew + @Char;
  END;

  PRINT @StringNew

Answer 5

Try this It Worked

Create Function dbo.Split_On_Upper_Case(@Temp VarChar(100))
Returns VarChar(100)
AS
Begin

Declare @KeepValues as varchar(50)
Set @KeepValues = '%[^ ][A-Z]%'
While PatIndex(@KeepValues collate Latin1_General_Bin, @Temp) > 0
    Set @Temp = Stuff(@Temp, PatIndex(@KeepValues collate Latin1_General_Bin, @Temp) + 1, 0, ' ')

Return @Temp
End