Groovy JavaScript Regex Name Capitalisation Handling

Greetings!

A tidbit found by a friend of mine online, forming the basis for a small piece of work I’ve done this week surrounding name capitalisation. This was pulled from a stack overflow article so credit where credit is due for starters:

js-regex-for-human-names

This is fairly robust, covering Mc, Mac, O’s and double-barrelled, hyphenated names. It does capitalise the first character directly after an apostrophe (regardless of placement) which may or may not be a problem. As for usage, I went with the following setup (with the relevant JavaScript and jQuery hooks being properly abstracted in the production code of course).

Firstly, the example HTML structure:

<div id="container">
	<!--An example form illustrating the fixNameCasing function being called on a test forename, middle names and surname field (when focus is lost)-->
	<form action="/" method="post">
		<div>
			<label id="forename-txt-label">Forename:</label>
		</div>
		<div>
			<input id="forename-text" name="forename-text" class="control-top-margin fix-name-casing" type="text" />
		</div>
		<div>
			<label id="middlename-text-label">Middle names:</label>
		</div>
		<div>
			<input id="middlename-text" name="middlename-text" class="control-top-margin fix-name-casing" type="text" />
		</div>
		<div>
			<label id="surname-text-label">Surname:</label>
		</div>
		<div>
			<input id="surname-text" name="surname-text" class="control-top-margin fix-name-casing" type="text" />
		</div>
		<div>
			<button id="submit-button" type="submit" class="control-top-margin">Submit</button>
		</div>
	</form>
</div>

Then, our jQuery/JavaScript juicy bits:

<!--Bring jQuery into scope so we can hook up a function to relevant elements on 'blur' event (lost focus)-->
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.2/jquery.min.js"></script>
<script type="text/javascript">
	
	// The name casing fix function utilising regex
	function fixNameCasing(name) {
		var replacer = function (whole, prefix, word) {
			ret = [];
			
			if (prefix) {
				ret.push(prefix.charAt(0).toUpperCase());
				ret.push(prefix.substr(1).toLowerCase());
			}
			
			ret.push(word.charAt(0).toUpperCase());
			ret.push(word.substr(1).toLowerCase());
			return ret.join('');
		}
		var pattern = /\b(ma?c)?([a-z]+)/ig;
		return name.replace(pattern, replacer);
	}
	
	// On document ready rig up of relevant controls (based upon using the 'fix-name-casing' class) 'blur' event. When focus is lost, in a given control, we take the controls input and format it based on a return value from fixNameCasing
	$(function() {
		$(".fix-name-casing").blur(function() {
			$(this).val(fixNameCasing($(this).val()));
		});
	});

</script>

The results! Each field in the following screenshot received fully lowercase or uppercase input before being tabbed out of (i.e. lost focus):

Image showing name capitalisation of three example name fields.

Name Capitalisation Test Output.

Lastly, here’s the entire code snippet:

<!DOCTYPE html>
<html>
<head>
	<title>Name Capitalisation Test</title>
	<style type="text/css">
		
		.control-top-margin {
			margin-top: 5px;
		}
	
	</style>
	<!--Bring jQuery into scope so we can hook up a function to relevant elements on 'blur' event (lost focus)-->
	<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.2/jquery.min.js"></script>
	<script type="text/javascript">
		
		// The name casing fix function utilising regex
		function fixNameCasing(name) {
			var replacer = function (whole, prefix, word) {
				ret = [];
				
				if (prefix) {
					ret.push(prefix.charAt(0).toUpperCase());
					ret.push(prefix.substr(1).toLowerCase());
				}
				
				ret.push(word.charAt(0).toUpperCase());
				ret.push(word.substr(1).toLowerCase());
				return ret.join('');
			}
			var pattern = /\b(ma?c)?([a-z]+)/ig;
			return name.replace(pattern, replacer);
		}
		
		// On document ready rig up of relevant controls (based upon using the 'fix-name-casing' class) 'blur' event. When focus is lost, in a given control, we take the controls input and format it based on a return value from fixNameCasing
		$(function() {
			$(".fix-name-casing").blur(function() {
				$(this).val(fixNameCasing($(this).val()));
			});
		});

	</script>
</head>
<body>
	<div id="container">
		<!--An example form illustrating the fixNameCasing function being called on a test forename, middle names and surname field (when focus is lost)-->
		<form action="/" method="post">
			<div>
				<label id="forename-txt-label">Forename:</label>
			</div>
			<div>
				<input id="forename-text" name="forename-text" class="control-top-margin fix-name-casing" type="text" />
			</div>
			<div>
				<label id="middlename-text-label">Middle names:</label>
			</div>
			<div>
				<input id="middlename-text" name="middlename-text" class="control-top-margin fix-name-casing" type="text" />
			</div>
			<div>
				<label id="surname-text-label">Surname:</label>
			</div>
			<div>
				<input id="surname-text" name="surname-text" class="control-top-margin fix-name-casing" type="text" />
			</div>
			<div>
				<button id="submit-button" type="submit" class="control-top-margin">Submit</button>
			</div>
		</form>
	</div>
</body>
</html>

The likelihood is that I’ll be using this just as a basis for my current requirements and adjusting as needed.

I hope this proves useful and kudos to my friend who found this and the original stackoverflow contributor. If anyone has any other examples of code that tackles this problem, that they would like to contribute, just let me know by commenting below.

Cheers!

T-SQL MERGE…Completely overlooked!

Good evening all,

Umm…how an earth did I miss this language feature? Where have I been since the release of SQL Server 2008? This really did just pass me by. I’m in the position whereby I don’t often run into this situation; where a T-SQL MERGE statement is directly applicable (i.e. table synchronisation), but still, I was a little embarrassed. Perhaps I have seen this at some point and it’s just been pushed out of my noodle at the expense of some other piece of information. So, red-faced (at least I am), let’s jump right in.

What is it?

In simple terms, this provides a compound way to perform an INSERT, UPDATE or DELETE on a target table, based on a source table. It’s as easy as that; we’ve negated the need for existence checks and other wrapping code/constructs, including the multiple INSERT, UPDATE and DELETE clauses of course. Performance is mentioned on the appropriate MSDN page. However, in my limited testing, I have not yet seen any major issues; just keep this thought on your mental radar anyway.

More information can be found here, so dive in and have a read (which details performance tips, so keep this in mind): T-SQL MERGE

MERGE Example

The following script rigs up some test data, in two example ‘target’ and ‘source’ tables, in an effort to demonstrate the synchronisations capabilities of the MERGE statement (apologies for the colour coding issues, I am using the correct language type but it seems to be taking exception to some of the content!).

/* 
	Very basic MERGE example
	-----------------------------

	Imagine these tables exist across databases, etc. and require synchronisation (or, as per my recent usage, I built up a derived table using CTE's to provide a source data table to compare against the target table to drive modifications)
	NOTE: Tables created for illustration only; not representative of how to structure this in practice of course
*/

-- TARGET TABLE
IF OBJECT_ID('dbo.TARGET_PERSON_TABLE') IS NULL
BEGIN

	CREATE TABLE dbo.TARGET_PERSON_TABLE
	(
		TARGET_PERSON_ID				INT		PRIMARY KEY
		, TARGET_FORENAME				NVARCHAR(255)
		, TARGET_SURNAME				NVARCHAR(255)
		, TARGET_AGE					INT			
	);

END;

-- SOURCE TABLE
IF OBJECT_ID('dbo.SOURCE_PERSON_TABLE') IS NULL
BEGIN

	CREATE TABLE dbo.SOURCE_PERSON_TABLE
	(
		SOURCE_PERSON_ID				INT		PRIMARY KEY
		, SOURCE_FORENAME				NVARCHAR(255)
		, SOURCE_SURNAME				NVARCHAR(255)
		, SOURCE_AGE					INT			
	);

END;

--Arrange some test data into each table (target and source, for illustration)
INSERT INTO dbo.TARGET_PERSON_TABLE
(
	TARGET_PERSON_ID
	, TARGET_FORENAME
	, TARGET_SURNAME
	, TARGET_AGE
)
VALUES
(
	1
	, 'Dave'
	, 'Jones'
	, 32
)
,
(
	2
	, 'Moira'
	, 'Stevens'
	, 27
)
,
(
	3
	, 'Larry'
	, 'Bodsworth'
	, 48
);

INSERT INTO dbo.SOURCE_PERSON_TABLE
(
	SOURCE_PERSON_ID
	, SOURCE_FORENAME
	, SOURCE_SURNAME
	, SOURCE_AGE
)
VALUES
(
	2
	, 'Mandy'
	, 'Stevens'
	, 32
)
,
(
	3
	, 'Larry'
	, 'Rodsworth'
	, 50
)
,
(
	4
	, 'Sandy'
	, 'Ennis'
	, 29
)
,
(
	5
	, 'Wendy'
	, 'Wainwright'
	, 40
);

-- Inspect the target/source table data prior to the MERGE operation
SELECT 
tpt.TARGET_PERSON_ID
, tpt.TARGET_FORENAME
, tpt.TARGET_SURNAME
, tpt.TARGET_AGE
FROM dbo.TARGET_PERSON_TABLE tpt;

SELECT
spt.SOURCE_PERSON_ID
, spt.SOURCE_FORENAME
, spt.SOURCE_SURNAME
, spt.SOURCE_AGE
FROM dbo.SOURCE_PERSON_TABLE spt;

-- Synchronise the target table with the source table, performing matching and INSERT, UPDATE and DELETE operations as required
MERGE dbo.TARGET_PERSON_TABLE tpt
USING SOURCE_PERSON_TABLE spt ON tpt.TARGET_PERSON_ID = spt.SOURCE_PERSON_ID 
	-- If a row is 'matched' (based on the above 'ON' statement) then simply update the target Person
	WHEN MATCHED 
	THEN UPDATE 
		SET 
		tpt.TARGET_FORENAME = spt.SOURCE_FORENAME
		, tpt.TARGET_SURNAME = spt.SOURCE_SURNAME
		, tpt.TARGET_AGE = spt.SOURCE_AGE
	-- If a row is 'not matched' (based on the above 'ON' statement) then do an insert of a new Person into the target table, based on the source table
	WHEN NOT MATCHED
		THEN INSERT
		(
			TARGET_PERSON_ID
			, TARGET_FORENAME
			, TARGET_SURNAME
			, TARGET_AGE
		)
		VALUES
		(
			SOURCE_PERSON_ID
			, SOURCE_FORENAME
			, SOURCE_SURNAME
			, SOURCE_AGE
		)
	-- Lastly, if a the target table contains a row not matched by the source table then remove the target table row entirely
	WHEN NOT MATCHED BY SOURCE 
		THEN DELETE;

/*
	Inspect the target/source table data post MERGE operation (values should be synchronised between correctly between the two tables)

	Expected Results
	----------------------
	1) Person ID 1, Dave Jones, should be removed from the target table (DELETE)
	2) Person ID 2, Forename of 'Moira' should be updated to 'Mandy' and Age should be updated from 27 to 32 (UPDATE)
	3) Person ID 3, Surname of 'Bodsworth' should be updated to 'Rodsworth' and Age should be updated from 48 to 50 (UPDATE)
	4) Person ID 4, Sandy Ennis, should be added to the target table (INSERT)
	5) Person ID 5, Wendy Wainwright, should be added to the target table (INSERT)

*/

SELECT 
tpt.TARGET_PERSON_ID
, tpt.TARGET_FORENAME
, tpt.TARGET_SURNAME
, tpt.TARGET_AGE
FROM dbo.TARGET_PERSON_TABLE tpt;

SELECT
spt.SOURCE_PERSON_ID
, spt.SOURCE_FORENAME
, spt.SOURCE_SURNAME
, spt.SOURCE_AGE
FROM dbo.SOURCE_PERSON_TABLE spt;

-- Clear down the tables post operation, for ease of re-running and re-testing
TRUNCATE TABLE dbo.TARGET_PERSON_TABLE;
TRUNCATE TABLE dbo.SOURCE_PERSON_TABLE;

Here is the state of the target and source tables prior to the MERGE statement being executed:

Illustrates the target and source tables state after the merge process

Target and Source Tables Post Merge.

After execution, you can see the effects of the synchronisation between the target and source tables:

Illustrates the target and source tables state before the merge process

Target and Source Tables Prior to Merging.

If this was useful to you then that’s superb…my guess is that a fair few of you will be shaking your heads wondering which hole in the sand I’ve had my head planted in for the last few years. Well, I call it how I see it and I’m pretty sure I missed the memo, so for you others out there that may have missed this, enjoy!

I’m still playing around with a F#/FitBit related piece at the moment, and I really want it to be a good quality post, so hang on in there as it’s coming soon.

Cheery bye!