So, you're saying that you have this one database (just one table? multiple tables?), and when you query to get strings from it (from just one column? from multiple columns?), you sometimes get utf8 strings, and sometimes get cp1252 strings, and sometimes get character entity references like ® (and sometimes numeric references like þ or þ?). Can all the variation occur within a single column, or is it different depending on which column holds the string?

And have you decided what format you want to normalize to? If so, what is that? (If not, why not?)

If there's really no way to predict what sort of encoding is coming back from the database for a given query, then you really do have one totally fubar'd database. What a shame.

I gather you've done some diagnosis of database contents, and have some idea about the scope of variation. Is stuff still being added to it? If so, does it continue to be as messy and uncontrolled as the stuff that's already there?

Don't feel like you have to tell us the answers to all those questions - those are just the main things you have to think about because they affect what kind(s) of solution(s) are likely to be useful.

Let's suppose you want to your "normalized format" to be just utf8 characters (no entities like & ® &#xf8ff etc.)

In terms of checking what needs to be done to a given string in order to get to that normalized form, there are a few handy guidelines:

• For any string that contains non-ASCII bytes, it's almost impossible for a non-utf8 string to be mistaken as utf8 data. Do this on every string from the database that has any non-ASCII content:

  eval { decode('utf8', $string, Encode::FB_CROAK) };
  [download]

  and then check $@. If it's true (meaning that the eval block died), then the string is definitely not utf8; if it's false, the eval block didn't die, and you can be reasonably sure that the string is utf8-encoded.
• If you happen to know that a non-ASCII string that is also non-utf8 is bound to be cp1252, then go ahead and decode from cp1252 to utf8.
• Once the non-ASCII content (if any) is in utf8, decode character entities; you may have to do this more than once (I've seen text with stuff like á - if that can happen in your database, repeat this step until the output string matches the input string).

Once the string is purely utf8 characters with no entity references, it should be pretty easy to convert that, if necessary, to any other form that you may need for a web display. Good luck.

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.