CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlcase-sensitive
user3513237
user3513237

Reputation: 1095

How to find more than 1 uppercase character

I'm running a series of SQL queries to find data that needs cleaning up. One of them I want to do is look for:

For example my name should be "John Doe". I would want it to find "JOhn Doe" or "JOHN DOE" or "John doe", but I would not want it to find "John Doe" since that is formatted correctly.

I am using SQL Server 2008.

Upvotes: 4

Views: 2045

Answers (5)

shA.t
shA.t

Reputation: 16958

I use this way:

;WITH yourTable AS(
    SELECT 'John Doe' As name
    UNION ALL SELECT 'JOhn Doe'
    UNION ALL SELECT 'JOHN DOE'
    UNION ALL SELECT 'John doe'
    UNION ALL SELECT 'John DoE'
    UNION ALL SELECT 'john Doe'
    UNION ALL SELECT 'jOhn dOe'
    UNION ALL SELECT 'jOHN dOE'
    UNION ALL SELECT 'john doe'
)
SELECT name
FROM (
    SELECT  name,
            LOWER(PARSENAME(REPLACE(name, ' ', '.'), 1)) part2,
            LOWER(PARSENAME(REPLACE(name, ' ', '.'), 2)) part1
    FROM yourTable) t
WHERE name COLLATE Latin1_General_BIN = UPPER(LEFT(part1,1)) + RIGHT(part1, LEN(part1) -1) + 
                                  ' ' + UPPER(LEFT(part2,1)) + RIGHT(part2, LEN(part2) -1)

Note:
This will be good for just two parted names for more, it should improved.

Upvotes: 0

elixenide
elixenide

Reputation: 44823

You can use a regular expression. I'm not a SQL Server whiz, but you want to use RegexMatch. Something like this:

select columnName
from tableName
where dbo.RegexMatch( columnName, 
        N'[A-Z]\W[A-Z]' ) = 1

Upvotes: 1

JBrooks
JBrooks

Reputation: 10013

First, I think you should make a function that returns a proper name (sounds like you need one anyway). See here under the heading "Proper Casing a Persons Name". Then find the ones that don't match.

SELECT Id, Name, dbo.ProperCase(Name)
FROM MyTable
WHERE Name <> dbo.PoperCase(Name)  collate Latin1_General_BIN

This will help you clean up the data and tweak the function to what you need.

Upvotes: 1

lc.
lc.

Reputation: 116438

The key is to use a case-sensitive collation, i.e. Latin1_General_BIN*. You can then use a query with a LIKE expression like the following (SQL Fiddle demo):

select *
from foo
where name like '%[A-Z][A-Z]%' collate Latin1_General_BIN --two uppercase in a row
or name like '% [a-z]%' collate Latin1_General_BIN --space then lowercase

*As per How do I perform a case-sensitive search using LIKE?, apparently there is a "bug" in the Latin1_General_CS_AS collation where ranges like [A-Z] fail to be case sensitive. The solution is to use Latin1_General_BIN.

Upvotes: 3

sqluser
sqluser

Reputation: 5672

If your goal is to update your column to capitalize the first character of each word (in your case firstName and lastName) , you can use the following query.

Create a sample table with data

Declare @t table (Id int IDENTITY(1,1),Name varchar(50))
insert into @t (name)values ('john doe'),('lohn foe'),('tohnytty noe'),('gohnsdf fgedsfsdf')

Update query

UPDATE @t
SET name =  UPPER(LEFT(SUBSTRING(Name, 1, CHARINDEX(' ', Name) - 1), 1)) + RIGHT(SUBSTRING(Name, 1, CHARINDEX(' ', Name) - 1), LEN(SUBSTRING(Name, 1, CHARINDEX(' ', Name) - 1)) - 1) +
            ' ' +
            UPPER(LEFT(SUBSTRING(Name, CHARINDEX(' ', Name) + 1, 8000), 1)) + RIGHT(SUBSTRING(Name, CHARINDEX(' ', Name) + 1, 8000), LEN(SUBSTRING(Name, CHARINDEX(' ', Name) + 1, 8000)) - 1)
FROM @t

Output 

SELECT * FROM @t

Id  Name
1   John Doe
2   Lohn Foe
3   Tohnytty Noe
4   Gohnsdf Fgedsfsdf

Upvotes: 0

Related Questions