在sql中用大写字母分割单词 [英] Split words with a capital letter in sql

问题描述

有谁知道如何从字符串中拆分以大写字母开头的单词?

Does anyone know 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

预期结果:

    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.

推荐答案

这是我创建的一个类似于去除非字母字符"的函数.如何剥离所有SQL Server 中字符串中的非字母字符?

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?

这个使用区分大小写的排序规则，它主动寻找非空格/大写字母组合，然后使用 STUFF 函数插入空格.这是一个标量 UDF，所以有些人会立即说它会比其他解决方案慢.对于这个概念，我说，请测试一下.此函数不使用任何表数据，仅根据需要循环多次，因此它可能会给您带来非常好的性能.

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

这样称呼它:

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

这篇关于在sql中用大写字母分割单词的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！