SQL Help needed

I have a problem well many, but the one I am most concerened about now is a work problem in that our Jetform server is falling over when it encounters a non alpha numeric in a name or address field. Irish people like O'Rielly cause trouble, hyphenated names, preferred names in brackets, addresses such as "Rocky Meadows" Long Road Outback NSW 2999.

Do you know any trick SQL that can pick out non alpha numeric characters in a field.

Something like select * from table
where surname LIKE '%non_alpha_numeric%'
I got no idea other than manually put in all the different possible characters one by one.

its a DB2 database.

The SQL itself shouldn't fall over as these are all legal characters for a varchar datafield, it is more likely that the software that uses the retrieved data is having trouble.

If this is the case it may be simpler to filter out the non alphanumeric chars after the data is retrieved by using something like a a string replace function or a regex function.

What language is the code written in? I can probably give you a solution if you give a bit more info.

Could you write a script to parse the data without the punctuation?
Just an idea...

Jetforms syntax is

if(@Address1. != ""), "print this", "else print this"

If someones address is
Chris Fraser
"Strawberry Fields"
Winding Road
Out West 9999

then the address is screwed up, the if statement gets printed on the letter. The same problem occurs when someones name is in the system as Michelangelo (Mick) Spaghetti. The brackets screw it up. One of Australia's largest super funds is manually rewriting letters because there is no workaround for this problem on the Jetforms side.

There are no problems with the data being in the database. No problems retrieving it, and yes all the non alpha numerics will have to be stripped out in the program code... which is CTL, a product of syncsoft. There are no problems at that end.

The shithouse BA I am working with wants to know how many members are affected by this problem. Currently data is very inconsistent. Eg some people are in system as O'BRIEN, some as O BRIEN, and some OBRIEN. Other than apostrophes, quotes, brackets and hyphens I doubt that there are any other issues. But they dont call em analysts for nothing.

selecting where field like (%''%) and like (%(%) will get me 99.9% of cases if not 100%. But what if there are other characters in there that could cause drama. I didnt want to have to write sql for every single different non alpha character. A select that would pick out any non alpha numeric would be schweet, but google is not my friend. I dont think one exists.

Sounds like you need to filter out the non alpha numeric chars using a regular expressions (regex) statement after you have retrieved the data from the database.

The statement [^a-zA-Z] will match any character that is not from a-z in both upper and lower case. To implement it you will need to find the correct syntax for whatever language you are programming in.

For example, in php the syntax would be

ereg_replace ('[^a-zA-Z0-9]', '', $your_string).

This would replace everything that is not a letter or a number in $your_string with nothing.

As far as I can tell the only regex functions available directly to SQL are % which matches any string and _ which matches any single character.

HOWEVER!!!

If you are using M$ SQL (excuse me whilst I spit on the floor) you can install any number of third party addons to give regex capability. I can't personally recommend any as I don't use M$ SQL but there appears to be plenty about.

Alternatively if you simply want to count affected records you could write a simple script to check if each record only contained numbers or letters.

My recommendation is to use the regex to filter out the unwanted chars, it's simpler and faster.

ereg_replace ('[^a-zA-Z0-9]', '', $your_string).

which also removes all spaces, commas, newlines....

Whilst a good regex will work, surely this problem is best tackled at the point where it is actually breaking which, by the sound of it, is Jetforms. Otherwise you have to introduce arbitrary restricticoons on what the address field can hold. Who's to know, for example, whether Burnaby-Funkwinkle is an acceptable surname or not...

Just my 2 cents...

hth

[ Edited on 25-3-07 by modulus ]