Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Split words with a capital letter in sql

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

Example:

    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.

like image 231
jackCaller Avatar asked May 05 '14 10:05

jackCaller


People also ask

How do you capitalize letters in SQL?

SQL Server UPPER() Function The UPPER() function converts a string to upper-case.

Does SQL care about capitalization?

Keywords in SQL are case-insensitive for the most popular DBMSs. The computer doesn't care whether you write SELECT , select, or sELeCt ; so, in theory, you can write however you like.


1 Answers

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)
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

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')
like image 132
George Mastros Avatar answered Oct 05 '22 22:10

George Mastros