jackCaller jackCaller - 10 months ago 55
SQL Question

Split words with a capital letter in sql

anyone knows how to split words starting with capital letters from a string?


DECLARE @var1 varchar(100) = 'OneTwoThreeFour'
DECLARE @var2 varchar(100) = 'OneTwoThreeFourFive'
DECLARE @var3 varchar(100) = 'One'

SELECT @var1 as Col1, <?> as Col2
SELECT @var2 as Col1, <?> as Col2
SELECT @var3 as Col1, <?> as Col2

expected result:

Col1 Col2
OneTwoThreeFour One Two three Four
OneTwoThreeFourFive One Two Three Four Five
One One

If this is not possible (or if too long) an scalar function would be okay as well.

Thanks in advance.


Here is a function I created that is similar to the "removing non-alphabetic characters". How to strip all non-alphabetic characters from string in SQL Server?

This one uses a case sensitive collation which actively seeks out a non-space/capital letter combination and then uses the STUFF function to insert the space. This IS a scalar UDF, so some folks will immediately say that it will be slower than other solutions. To that notion, I say, please test it. This function does not use any table data and only loops as many times as necessary, so it will likely give you very good performance.

Create Function dbo.Split_On_Upper_Case(@Temp VarChar(1000))
Returns VarChar(1000)

    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

Call it like this:

Select dbo.Split_On_Upper_Case('OneTwoThreeFour')
Select dbo.Split_On_Upper_Case('OneTwoThreeFour')
Select dbo.Split_On_Upper_Case('One')
Select dbo.Split_On_Upper_Case('OneTwoThree')
Select dbo.Split_On_Upper_Case('stackOverFlow')
Select dbo.Split_On_Upper_Case('StackOverFlow')