Find Only Capital Letters in Word Through in SQL Server Query

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.

Return word with the capital letter from string

You would need PATINDEX (to find any of the capital letters within the string), CHARINDEX (to find the position of space after the capital letter or end of the string) and SUBSTRING (to get the part of the whole string).

try the following:

declare @tab table (column1 varchar(100))
insert into @tab select 'this is a New world'
insert into @tab select 'this is a kind Person'
insert into @tab select 'this is a good Idea'

select * from @tab

select substring(column1, PATINDEX('%[A-Z]%', column1 COLLATE Latin1_General_BIN)
                , case when CHARINDEX(' ', substring(column1, PATINDEX('%[A-Z]%', column1 COLLATE Latin1_General_BIN), len(column1)), 1) = 0
                        then len(column1) else CHARINDEX(' ', substring(column1, PATINDEX('%[A-Z]%', column1 COLLATE Latin1_General_BIN), len(column1)), 1)-1 end) as column1
from @tab

Select only rows with each word beginning with capital letter being output

If you are using SQL Server:-

Table Creation:

create table YourTable(id int identity(1,1),YourColumn varchar(100));

insert into YourTable(YourColumn)
select 'One Letter' Name union all
select 'another One' union all
select 'lower case' union all
select 'Upper case Sensitiive charecters'

Fetch Data:

with SPosition as
(select patindex('% %',YourColumn) SPosition, YourColumn from YourTable 
union all
select SPosition+patindex('% %',substring(YourColumn, SPosition+1, len(YourColumn))) SPosition, YourColumn from SPosition
where patindex('% %',substring(YourColumn, SPosition+1, len(YourColumn)))>0
),SelectedWords as(
select substring(YourColumn,SPosition+1,1) StartingPosition,Case When substring(YourColumn,SPosition+1,1) Collate Latin1_General_CS_AI=upper(substring(YourColumn,SPosition+1,1))
then isnull(nullif(ltrim(rtrim(substring(YourColumn,SPosition+1,patindex('% %',substring(YourColumn, SPosition+1, len(YourColumn)))))),''),
substring(YourColumn,SPosition+1,len(YourColumn)))
else null end Words,
SPosition,YourColumn from SPosition
union all
select substring(YourColumn,1,1),Case When substring(YourColumn,1,1) Collate Latin1_General_CS_AI=upper(substring(YourColumn,1,1))
then isnull(nullif(ltrim(rtrim(substring(YourColumn,1,patindex('% %',substring(YourColumn, 1, len(YourColumn)))))),''),
substring(YourColumn,1,len(YourColumn)))
else null end,
1,YourColumn from YourTable
)
select YourColumn,String_Agg(Words,',') from SelectedWords where Words is not null
group by YourColumn

You can run this code by Click Here

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

How to find values in all caps in SQL Server?

You can force case sensitive collation;

select * from T
  where fld = upper(fld) collate SQL_Latin1_General_CP1_CS_AS

Locate upper case characters in SQL Server database field

You can do a binary comparison using:

select *
from Cust
where cast(Surname as varbinary(120)) != cast(lower(Surname) as varbinary(120))

---

Related Topics