Split Words with a Capital Letter in SQL

Split words with a capital letter in sql

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

Split the camel cased sentence into words

Try with the following query:

select regexp_replace('IAmGoodBoy','([A-Z][^A-Z]*)','\1 ') from dual;

Split words with a capital letter in PostgreSQL

This should do the trick:

select regexp_replace('ThoMasTest', '([a-z])([A-Z])', '\1 \2','g');

The expression matches two characters next to eachother, each one in its own group:

1. [a-z] that matches a lowercase letter.
2. [A-Z] finds a capital letter

So if one lowercase if immediately followed by a capital letter insert a space between them.

Do that globally 'g'.

Split by uppercase Oracle

Maybe this is the regexp you are looking for

"Insert a blank between each lower case character followed by an upper case character":

select regexp_replace('IsThisAnExample', '([[:lower:]])([[:upper:]])', '\1 \2') from dual

First character can simply replaced by an upper case letter by

select upper(substr('isThisAn Example', 1,1))||substr('isThisAn Example', 2) from dual;

So, first replace the first character and regexp_replace for the result:

select regexp_replace(upper(substr('isThisAn Example', 1,1))||substr('isThisAn Example', 2), '([[:lower:]])([[:upper:]])', '\1 \2') from dual;

If only the first character of your sentence should be an upper case letter, then try:

select upper(substr(regexp_replace('IsThisAnExample', '([[:lower:]])([[:upper:]])', '\1 \2'),1,1))||
       lower(substr(regexp_replace('IsThisAnExample', '([[:lower:]])([[:upper:]])', '\1 \2'),2))
 from dual

Splitting string column with uppercase and lowercase string into two separate columns Pyspark/Python/Sql?

I used a regex statement to split the strings. You can use the re.group(x) method to access the two groups. Here is some more information: https://docs.python.org/3/library/re.html

import re

strings = ["MEM-BEN-BTN-CLK-entertainment-audible",
    "MEM-BEN-LOC-MODAL-LOCATION-INPUT-Birmingham, AL, USA",
    "MEM-BEN-BTN-CLK-entertainment-games",
    "MEM-BEN-BTN-CLK-healthandwellness-love-and-meaning-after-50",
    "MEM-BEN-BTN-LRN-learn-more-aarp-travel-center-powered-by-expedia-10083",
    "MEM-BEN-BTN-LRN-learn-more-embassy-suites-by-hilton-1019"]

regex = "(?P<Click_Upper>[A-Z\-]+)-(?P<Click_Lower>.*)"

for string in strings:
    print(re.match(regex,string).groups())

Here is the output:

('MEM-BEN-BTN-CLK', 'entertainment-audible')
('MEM-BEN-LOC-MODAL-LOCATION-INPUT', 'Birmingham, AL, USA')
('MEM-BEN-BTN-CLK', 'entertainment-games')
('MEM-BEN-BTN-CLK', 'healthandwellness-love-and-meaning-after-50')
('MEM-BEN-BTN-LRN', 'learn-more-aarp-travel-center-powered-by-expedia-10083')
('MEM-BEN-BTN-LRN', 'learn-more-embassy-suites-by-hilton-1019')

How to get only Capital letters from given value

This code may help you..

 declare @input as varchar(1000) -- Choose the appropriate size
  declare @output as varchar(1000) -- Choose the appropriate size

 select @input = 'Investigations and Remedial Measures', @output = ''

 declare @i int

select @i = 0

while @i < len(@input)
begin
select @i = @i + 1

select @output = @output +  case when  unicode(substring(@input, @i, 1))between 65 
   and 90 then substring(@input, @i, 1)  else '' end

end

SELECT @output

SQL Select only the words that are in Capital

DECLARE @searchtext VARCHAR(100) = 'THIS SENTENCE IS IN UPPERCASE and this in lower case'
DECLARE @i INT = 1, @l INT = LEN(@searchtext)

WHILE (@i <= @l AND 1 = CHARINDEX(UPPER(LEFT(@searchtext,@i)),@searchtext COLLATE Latin1_General_CS_AS))
BEGIN
    SET @i = @i+1
END

SELECT RTRIM(LEFT(@searchtext, @i-1))

I can't get it to work with PATINDEX btw., no matter where I put the collation info.

---

Related Topics