Postgresql – Postgres normalize human name string

normalizationpostgresql

Is there some function to normalize name string:

 select normalize('R.C. Bray') 

 'R. C. Bray'

Or the opposite direction?

The simple replace('. ', '.') or replace('.', '. ') is wrong and may be effect another data.

Best Answer

regexp_replace should be enough:

regexp_replace('R.C. Bray.', '([A-Z])(\.)(\w)', '\1\2 \3', 'g')

This replaces a single upper case letter followed by a . directly followed by a "word character" by inserting a space between the dot and the character. It leaves dots that have a whitespace after them untouched.

Related Solutions

How to normalize data based only on the repetitions

I've had to reverse engineer several existing complex data sets. The most important thing to establish are the keys and dependencies in the data. The problem is frankly NP hard, so some intuition and inspection will go a long way to getting you to a sensible answer, so don't count on a simple "turn the crank" solution unless you've got a lot of time on your hands.

What you need to do is to query the data a column at a time and by combinations of columns. You want frequency distributions for column values (and column combination values). Columns (or combos) with maximum frequencies of 1 are candidate keys.

You can also look at frequency distributions of combinations of columns to find potential hierarchies. In your example each value in Col2 only ever has one value in Col1 and so forth.

When you identify candidate keys and dependencies between columns you can apply normalization.

EDIT: In response to OP's quesiton in comments:

This is a query that would tell you whether or not one column may have a functional dependency on another column:

select COL2 
from MYSTERY
group by COL2
having count(distinct(COL1)) <> 1

If COL2 has a functional dependency on COL1 then this query would return 0 rows. This is because every value of COL2 has exactly 1 corresponding value of COL1.

MySQL – Should String Arrays Be Normalized or Kept Simple?

A couple of things you might want to consider:

Maintenance of the data.

If the data is going to change often then it would be easier and quicker to have the data normalised so you only have to change it in one place and have all the usages of it automatically update. Conversely, if the data hardly ever changes then this is not a consideration.
Full text searching.

This should be quicker than doing like '%string%' searches and, depending on your database and what's available, may dictate the format you have store your data in.

In addition there's the obvious thing that storing the text repeatedly in the table(s) will increase the size of your database. This may have an impact on performance or cost you more to make sure you have enough disk space and/or memory available on your server.

Wikipedia has an article on the basics. It's not an area I'm terribly familiar with, just that a colleague is looking into this and there are trade-offs with how the data is organised when doing other searches. Other links - MSDN, MySQL and PostgreSQL

Best Answer

Related Solutions

How to normalize data based only on the repetitions

MySQL – Should String Arrays Be Normalized or Kept Simple?

Related Question