How to find more than one uppercase character
I am running a series of SQL queries to find the data that needs to be cleaned up. One that I want to do is search:
- 2 or more lowercase letters per line
- starting with a lowercase letter
- space followed by a lowercase letter
For example, my name should be "John Doe". I would like him to find "JOhn Doe" or "JOHN DOE" or "John doe", but I would not want him to find "John Doe" as it is formatted correctly.
I am using SQL Server 2008.
source to share
The key is for using case sensitive sort, i.e. Latin1_General_BIN
* . Then you can use a query with an expression LIKE
as shown below ( 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
* How How do I perform a case sensitive search using LIKE? appears to be in Latin1_General_CS_AS
, where ranges such as [A-Z]
are case insensitive. The solution is to use Latin1_General_BIN
.
source to share
You can use regular expression. I'm not whiz SQL Server, but you want to use RegexMatch . Something like that:
select columnName
from tableName
where dbo.RegexMatch( columnName,
N'[A-Z]\W[A-Z]' ) = 1
source to share
First, I think you should create a function that returns its own name (anyway it looks like you need it). See here under the heading "Proper Cover of a Person's 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 clear the data and customize the function to what you want.
source to share
If your goal is to update the column to use the first character of each word (in your case firstName and lastName), you can use the following query.
Create sample data table
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
source to share
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 only be useful for two separated names for more, this should improve.
source to share