Topic: SQL: query for non-alphanumerics

[ober]

How do you query for values that are not alphanumeric?  One of my users uploaded a file and I never got around to restricting the input to alphanumeric and now there is a bunch of gibberish shit in the database.  Help.

I fucking hate this shit.

[Mike]

How do you query for values that are not alphanumeric?  One of my users uploaded a file and I never got around to restricting the input to alphanumeric and now there is a bunch of gibberish shit in the database.  Help.

I fucking hate this shit.

So you are looking for any row that doesn't contain alphanumeric characters?

Maybe something like

Code: [Select]

SELECT foo
FROM bar
WHERE baz REGEXP '[^A-Za-z0-9 ]'

You can add in any other symbols you do want to allow.

Do you know what type of files they uploaded?  Might be easier to search for the identifying parts of those files.

[Steve]

Why didn't you just restrict the input while you were doing it?

[ober]

Thanks Mike, I'll try that.  They're supposed to be very basic CSV files.  It appears as though he might be uploading some Excel files though based on some of what is ending up in the DB.  At least I can identify the file being uploaded because he has to select a specific company before uploading.

Steve, because I'm lazy and it's one person uploading a few dozen files and I didn't think he could fuck it up.

[ober]

That worked Mike.  I didn't realize you could do REGEX in SQL WHERE clauses.

[Steve]

....one person........ uploading a few dozen files and I didn't think he could fuck it up.

How long have you been doing this???