Stripping out all non-alpha numeric characters from the field

[markros]

Hi everybody,

I haven't worked with MySQL before, I worked with other databases (SQL Server, etc.).

I have a task to remove all non-alphanumeric chars from the field.

I've just installed MySQL tools, but I'm not yet sure how to connect to the server - need to check with my colleague here.

Any ideas would be much appreciated.

 

[r937]

this is something that

1. should be done by the application language before new values are inserted or updated

2. if needs fixing, will need it only once, and therefore should be done by the application language

mysql has REGEXPs but the do not update

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[markros]

Can you do in mySQL:

update myTable set myFieldNoDashes = dbo.myUDF(myFieldNoDashes) ?

What is the syntax of calling UDF in mySQL and how can I get MySQL functions help without installing MySQL locally (I did install MySQL tools to connect to the server).

My colleague found this function

http://forums.mysql.com/read.php?118,229993,240768#msg-240768

and knowing this

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/sql-server-extracting-data-again

I'm wondering if this UDF can be optimized a bit or MySQL doesn't support RegEx inside Substring_Index function?

Basically, we're doing import about once a month:

1. Clean the existing tables, append from CSV tables, update some fields.

So, can you please clarify how can I call UDF in the UPDATE and if this UDF is the best we can have or it can be improved.

Thanks in advance.