4

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:

  • 2 or more uppercase letters in a row
  • starting with a lowercase letter
  • space then a lowercase letter

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.

shA.t
  • 16,580
  • 5
  • 54
  • 111
user3513237
  • 995
  • 3
  • 9
  • 26

5 Answers5

3

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.

Community
  • 1
  • 1
lc.
  • 113,939
  • 20
  • 158
  • 187
1

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
elixenide
  • 44,308
  • 16
  • 74
  • 100
  • [More details :] This answer needs a UDF that defined in the linked article ;). – shA.t May 12 '15 at 04:14
  • @shA.t Regarding the second comment: done. As for a UDF: I'm already way out of my depth on SQL Server --- I'm a MySQL guy, myself. On top of that, I'm currently just killing time on a mobile device. So, I'm afraid that's just beyond my expertise for tonight. :) – elixenide May 12 '15 at 04:25
1

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.

JBrooks
  • 9,901
  • 2
  • 28
  • 32
0

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
sqluser
  • 5,502
  • 7
  • 36
  • 50
0

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.

shA.t
  • 16,580
  • 5
  • 54
  • 111