Regular Expression for Capital Characters in String

Question

Regular Expression for Capital Characters in String

caippers

Mr or Mrs. 500

Points: 582
More actions
March 1, 2014 at 4:22 am

#304686

I need to verify data in a column and do pattern matching on the string in each field.
I've create a CLR Function that will verify the element against the patter and return a True or Fales....
I have only used reg expressions once and am struggling mightly. I'm bacially here. \A
I need to match a pattern that each word in the string will be a Capital letter.
ex. The beginning of the day - Fail
ex. The Beginning Of The Day - Pass
I've been referencing Microsoft site but am struggling to find a pattern that I can use (even though I'm sure it's there) . Could someone nudge me in the right direction or suggest some good reading on this.
http://msdn.microsoft.com/en-us/library/az24scfc(v=vs.110).aspx

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

caippers Mr or Mrs. 500 Points: 582 More actions · Answer 1

After messing with this all night I think I might be close but not sure

I believe what I have is to take the first character of the string ensure it's capitalized then ignore the following letters in the word as they can be lower case.

The part I'm struggling with is how to build this including spaces.

In my example 'The beginning Of The Day' it fails but it also fails if I have the correct pattern ' 'The Beginning Of The Day' so obviously it's not right.

'^([\p{Lu}\p{Lt}][\p{Ll}\p{Lm}\p{Lo}]+)+$'

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 2

caippers (3/1/2014)
After messing with this all night I think I might be close but not sure
I believe what I have is to take the first character of the string ensure it's capitalized then ignore the following letters in the word as they can be lower case.
The part I'm struggling with is how to build this including spaces.
In my example 'The beginning Of The Day' it fails but it also fails if I have the correct pattern ' 'The Beginning Of The Day' so obviously it's not right.
'^([\p{Lu}\p{Lt}][\p{Ll}\p{Lm}\p{Lo}]+)+$'

If you're doing this from SQL Server, there's no need for RegEx and CLRs. The following will also likely be faster that what you could do with RegEx and SQLCLR.

--===== Build and populate a small test table.

-- This is NOT a part of the solution.

SELECT 'The beginning of the day' SomeString

INTO #TestTable UNION ALL

SELECT 'The Beginning Of The Day' UNION ALL

SELECT 'The Beginning Of The day' UNION ALL

SELECT 'the Beginning Of The Day' UNION ALL

SELECT 'The BeginninG Of The Day' UNION ALL

SELECT 'The BegiNning Of The Day'

;

--===== This returns the correct PASS/FAIL conditions

SELECT SomeString

,PassFail =

CASE

WHEN SomeString COLLATE LATIN1_GENERAL_BIN LIKE '[a-z]%'

OR SomeString COLLATE LATIN1_GENERAL_BIN LIKE '% [a-z]%'

OR SomeString COLLATE LATIN1_GENERAL_BIN LIKE '%[^ ][A-Z]%'

THEN 'Fail'

ELSE 'Pass'

END

FROM #TestTable

;

Results:

SomeString PassFail

------------------------ --------

The beginning of the day Fail

The Beginning Of The Day Pass

The Beginning Of The day Fail

the Beginning Of The Day Fail

The BeginninG Of The Day Fail

The BegiNning Of The Day Fail

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)