Test for Upper Case - T-Sql
Using collations
eg:
if ('a'='A' Collate Latin1_General_CI_AI)
print'same 1'
else
print 'different 1'
if ('a'='A' Collate Latin1_General_CS_AI)
print'same 2'
else
print 'different 2'
The CS in the collation name indicates Case Sensitive (and CI, Case Insensitive). The AI/AS relates to accent sensitivity.
or in your example
SUBSTRING(author,1,1) <> LOWER(SUBSTRING(author,1,1)) COLLATE Latin1_General_CS_AI
SQL Server: Check Upper Case or Lower Case after certain Character
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 |
+----+--------------------------------------------------+
SQL - Find all UPPER CASE strings
You nailed it the first time.
SELECT * FROM MyTable WHERE Column1 = UPPER(Column1) COLLATE SQL_Latin1_General_CP1_CS_AS
The above is the simplest and appears to be the fastest. It would slow down by putting it into a function and now builtin function exists. The other answers are worth their merit for explanation reasons.
Edit:
Part 2 - The original questioner further asked "How do I search all tables & columns in the database?". Here is a quick way to find. If you want to return all fields that have all capitals simply remove "TOP 1" from the procedure below but beware. If you have more than a lot of records you will probably run out of memory.
CREATE PROCEDURE SP_SearchAllTablesForAFieldWithAllCapitals
AS
BEGIN
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
SET NOCOUNT ON
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128)
SET @TableName = ''
WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)
WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)
IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT TOP 1''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' = UPPER(' + @ColumnName + ') COLLATE Latin1_General_CS_AS'
)
END
END
END
SELECT ColumnName, ColumnValue FROM #Results
END
GO
EXEC SP_SearchAllTablesForAFieldWithAllCapitals
FYI: I used the query from here as a starting point.
How to search all text fields in a DB for some substring with T-SQL
SQL Server: Make all UPPER case to Proper Case/Title Case
Here's a UDF that will do the trick...
create function ProperCase(@Text as varchar(8000))
returns varchar(8000)
as
begin
declare @Reset bit;
declare @Ret varchar(8000);
declare @i int;
declare @c char(1);
if @Text is null
return null;
select @Reset = 1, @i = 1, @Ret = '';
while (@i <= len(@Text))
select @c = substring(@Text, @i, 1),
@Ret = @Ret + case when @Reset = 1 then UPPER(@c) else LOWER(@c) end,
@Reset = case when @c like '[a-zA-Z]' then 0 else 1 end,
@i = @i + 1
return @Ret
end
You will still have to use it to update your data though.
How to find rows that have a value that contains a lowercase letter
SELECT * FROM my_table
WHERE UPPER(some_field) != some_field
This should work with funny characters like åäöøüæï. You might need to use a language-specific utf-8 collation for the table.
How to check upper case existence length in a string - Sql Query
There is no built-in T-SQL function for that.
You can use a user-defined function like this one:
CREATE FUNCTION CountUpperCase
(
@input nvarchar(50)
)
RETURNS int
AS
BEGIN
declare @len int
declare @i int
declare @count int
declare @ascii int
set @len = len(@input)
set @i = 1
set @count = 0
while @i <= @len
begin
set @ascii = ascii(substring(@input, @i, 1))
if @ascii >= 65 and @ascii <= 90
begin
set @count = @count +1
end
set @i = @i + 1
end
return @count
END
Usage (with the examples from your question):
select dbo.CountUpperCase('KKart')
returns 2
.select dbo.CountUpperCase('WPOaaa')
returns 3
.
Related Topics
"Rolling Up" Groups in Jaspersoft Ireport
Syntax Error: Unexpected End of File
Syntax Error with Update Query When Join with Some Table
Least Value But Not Null in Oracle SQL
Is It Better to Do an Equi Join in the from Clause or Where Clause
Error "Value" Does Not Exist - Postgresql Insert into Issue
Table as an Argument of a Postgresql Function
SQL Query: Return Max Value Record of a Group
Oracle SQL: Variables Used in Place of Table Names
How to Remove Part of the String in Oracle
How to Find All Open/Active Connections in Db2 (8.X)
Select Top Rows Until Value in Specific Column Has Appeared Twice
Select Last Records from Table Using Group By
Concat the Second Column Value If the First Column Value Is Same
Update Table with Random Record in Update Statment in SQL Server
SQL Query - Select * from View or Select Col1, Col2, ... Coln from View