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.

回答1:

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

回答2:

Here is a function I have just created.

FUNCTION

CREATE FUNCTION dbo.Split_On_Upper_Case
 (
     @String VARCHAR(4000)
 )
RETURNS VARCHAR(4000)
AS
BEGIN

DECLARE @Char CHAR(1);
DECLARE @i    INT = 0;
DECLARE @OutString VARCHAR(4000) = '';


WHILE (@i <= LEN(@String))
BEGIN
    SELECT @Char = SUBSTRING(@String, @i,1)

    IF (@Char = UPPER(@Char) Collate Latin1_General_CS_AI) 
       SET @OutString = @OutString + ' ' + @Char;
    ELSE 
       SET @OutString = @OutString +  @Char;

     SET @i += 1;
END

 SET @OutString =  LTRIM(@OutString);

 RETURN @OutString;

END 

Test Data

DECLARE @TABLE TABLE (Strings VARCHAR(1000))
INSERT INTO @TABLE 
VALUES ('OneTwoThree')   ,
       ('FourFiveSix')   ,
       ('SevenEightNine')

Query

SELECT dbo.Split_On_Upper_Case(Strings) AS Vals
FROM @TABLE

Result Set

╔══════════════════╗
║       Vals       ║
╠══════════════════╣
║ One Two Three    ║
║ Four Five Six    ║
║ Seven Eight Nine ║
╚══════════════════╝

回答3:

If a single query is needed 26 REPLACE can be used to check every upper case letter like

SELECT @var1 col1, REPLACE(
                    REPLACE(
                     REPLACE(
                      ...
                       REPLACE(@var1, 'A', ' A')
                    , ... 
                  , 'X', ' X')
                , 'Y', ' Y')
              , 'Z', ' Z') col2

Not the most beautiful thing but it'll work.

EDIT
Just to add another function to do the same thing in a different way of the other answers

CREATE FUNCTION splitCapital (@param Varchar(MAX))
RETURNS Varchar(MAX)
BEGIN
  Declare @ret Varchar(MAX) = '';
  declare @len int = len(@param);

  WITH Base10(N) AS (
              SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 
    UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 
    UNION ALL SELECT 8 UNION ALL SELECT 9
  ), Chars(N) As (
    Select TOP(@len)
           nthChar 
         = substring(@param, u.N + t.N*10 + h.N*100 + th.N*1000 + 1, 1) 
           Collate Latin1_General_CS_AI
    FROM   Base10 u
           CROSS JOIN Base10 t
           CROSS JOIN Base10 h
           CROSS JOIN Base10 th
    WHERE  u.N + t.N*10 + h.N*100 + th.N*1000 < @len
    ORDER BY u.N + t.N*10 + h.N*100 + th.N*1000
  )
  SELECT @ret += Case nthChar 
                      When UPPER(nthChar) Then ' ' 
                      Else '' 
                 End + nthChar
  FROM   Chars

  RETURN @ret;
END

This one uses the possibility of TSQL to concatenate string variable, I had to use the TOP N trick to force the Chars CTE rows in the right order

回答4:

Build a Numbers table. There are some excellent posts on SO to show you how to do this. Populate it with values up the maximum length of your input string. Select the values from 1 through the actual length of the current input string. Cross join this list of numbers to the input string. Use the result to SUBSTRING() each character. Then you can either compare the resulting list of one-charachter values to a pre-populated table-valued variable or convert each character to an integer using ASCII() and choose only those between 65 ('A') and 90 ('Z'). At this point you have a list which is the position of each upper-case character in your input string. UNION the maximum length of your input string onto the end of this list. You'll see why in just a second. Now you can SUBSTRING() your input variable, starting at the Number given by row N and taking a length of (the Number given by row N+1) - (The number given by row N). This is why you have to UNION the extra Number on the end. Finally concatenate all these substring together, space-separated, using the algorithm of your choice.

Sorry, don't have an instance in front of me to try out code. Sounds like a fun task. I think doing it with nested SELECT statements will get convoluted and un-maintainable; better to lay it out as CTEs, IMHO.

回答5:

I using an ITVF function (Table Function). In terms of performance, the inline function works like a view

    CREATE FUNCTION [dbo].[udf_Split_Capitals_In_Str]
    (@str VARCHAR(8000))
    RETURNS TABLE AS RETURN 
    WITH Tally (n) AS
    (
        SELECT TOP (LEN (@str)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
        FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0)) a(n)
        CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n)
        CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) c(n)
        CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d(n)

    )
    SELECT New_Str =  STUFF((
        SELECT 
        CASE 
            WHEN SUBSTRING(@str, n,1) = UPPER(SUBSTRING(@str, n,1)) Collate Latin1_General_CS_AI AND n > 1 
            THEN ' ' + SUBSTRING(@str, n,1)  
            ELSE SUBSTRING(@str, n,1) 
        END
    FROM Tally
    FOR XML PATH ('')),1,0,'')

/*How To use:*/
SELECT * FROM dbo.udf_Split_Capitals_In_Str ('HelloWorld')
/*Cross Apply Example*/
    SELECT T.* , Fixed_Name.New_Str FixedName
    FROM 
        (       
        SELECT Id= 1 , Name = 'DonaldTrump'
        UNION ALL 
        SELECT Id= 2 , Name = 'HilaryClinton'
        ) T
    CROSS APPLY dbo.udf_Split_Capitals_In_Str (T.Name) Fixed_Name

回答6:

I know that there are already some good answers out there, but if you wanted to avoid creating a function, you could also use a recursive CTE to accomplish this. It's certainly not a clean way of doing this, but it works.

DECLARE
    @camelcase nvarchar(4000) = 'ThisIsCamelCased'
;

WITH
    split
AS
    (
        SELECT
              [iteration]   = 0
             ,[string]      = @camelcase

        UNION ALL

        SELECT
             [iteration]    = split.[iteration] + 1
            ,[string]       = STUFF(split.[string], pattern.[index] + 1, 0, ' ')
        FROM
            split
        CROSS APPLY
            ( SELECT [index] = PATINDEX(N'%[^ ][A-Z]%' COLLATE Latin1_General_Bin, split.[string]) )
            pattern
        WHERE
            pattern.[index] > 0
    )
SELECT TOP (1)
    [spaced] = split.[string]
FROM
    split
ORDER BY
    split.[iteration]   DESC
;

As I said, this isn't a pretty way to write a query, but I use things like this when I'm just writing up some ad-hoc queries where I would not want to add new artifacts to the database. You could also use this to create your function as an inline table valued function, which is always a tad nicer.

回答7:

Please Try This:

    declare  @t nvarchar (100) ='IamTheTestString'  
    declare  @len int
    declare  @Counter int =0 
    declare  @Final nvarchar (100) =''
    set @len =len( @t)


    while (@Counter <= @len)
    begin 

    set @Final= @Final + Case when  ascii(substring (@t,@Counter,1))>=65 and  
    ascii(substring (@t,@Counter,1))<=90 then ' '+substring (@t,@Counter,1) else 
    substring (@t,@Counter,1) end


    set @Counter=@Counter+1
    end

    print ltrim(@Final)