SQL Server: Check Upper Case or Lower Case after certain Character

Question

I have data like 'John is my name; Ram is my name; Adam is my name'.

My rule is such that every first letter that comes after ; should be capital letter.

How do I select all values that satisfy the rule?

Answer 1

The other answers show how to transform rows into something that matches your pattern.

If you just want to select the rows that match the pattern you are describing, you can use patindex() or like with a case sensitive collation (or use collate to apply one).

This assumes that in addition to the rule that every letter that follows a semicolon must be a capital letter, that the very first letter should also be capital. If that is not the case, just remove the first clause in the where.

select *
from t
where patindex('[ABCDEFGHIJKLMNOPQRSTUVWXYZ]%', val collate latin1_general_cs_as) = 1
  and patindex('%; [^ABCDEFGHIJKLMNOPQRSTUVWXYZ]%', val collate latin1_general_cs_as) = 0

---

select *
from t
where val collate latin1_general_cs_as like '[ABCDEFGHIJKLMNOPQRSTUVWXYZ]%'
  and val collate latin1_general_cs_as not like '%; [^ABCDEFGHIJKLMNOPQRSTUVWXYZ]%'

---

test setup:

create table t (id int not null identity(1,1),val varchar(256))
insert into t values 
 ('John is my name; Ram is my name; Adam is my name')
,('john is my name; ram is my name; adam is my name')

rextester demo: http://rextester.com/DBGIS10645

Both of the above return:

+----+--------------------------------------------------+
| id |                       val                        |
+----+--------------------------------------------------+
|  1 | John is my name; Ram is my name; Adam is my name |
+----+--------------------------------------------------+

Answer 2

You might split this with the XML trick like this

DECLARE @YourString VARCHAR(100)='John is my name; Ram is my name; Adam is my name';
WITH Splitted AS
(
    SELECT CAST('<x>' + REPLACE((SELECT REPLACE(@YourString,'; ','$$SplitHere$$') AS [*] FOR XML PATH('')),'$$SplitHere$$','</x><x>')+ '</x>' AS XML) AS Casted
)
,DerivedTable AS
(
    SELECT  ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS PartNr
           ,x.value(N'text()[1]',N'nvarchar(max)') AS Part
    FROM Splitted
    CROSS APPLY Casted.nodes(N'/x') AS X(x)
)
SELECT PartNr
      ,Part
      ,CASE WHEN ASCII(LEFT(Part,1)) BETWEEN ASCII('A') AND ASCII('Z') THEN 1 ELSE 0 END AS FirstIsCapital
FROM DerivedTable;

The result

Nr  Part            FirstLetterIsCaptial
---------------------------------------- 
1   John is my name     1
2   Ram is my name      1
3   Adam is my name     1

I do not know what your final goal is... Find parts, where the first letter is not capital? Make sure, that your rule is fullfilled?

However:
Best was, to use this to correct your design and place these parts in a 1:n related side table.