Removing special characters

[ash_19]

I want special characters to be removed while doing an insert or update in informix database.
Is there an sql wich can remove existing special characters in the database.
By special characters i mean all those characters which are not present on the keyboard of a PC

[Sanjay Kumar Suri]

To my knowldege there is no SQL which can do so. However check if Informix functions can provide such functionality.

Or a function in 4GL/"C" need to be written to get such functionality.

[Rajeev Shukla]

I am afraid there is no such sql utility or command to do so,
But you could try one thing which surely will work.
1. Unload the data from the table (dbunload)
2. then you can vi the file and remove the special characters you want.
3. Load the data back into the table.

[Graham Cameron_1]

In Oracle SQLPLUS we have the translate() function which maps characters to other characters (or to nothing - removing them). Does informix have this ?

-- Graham

[Sanjay Kumar Suri]

Dear all

Translate kind of function will work if the user is aware of all the special charachters to be removed while doing an insert or update.

In this case the user need a function which will ignore all but (A-Z, a-z, 0-0 and other characters on the keyboard).

The Pseudocode of the funtion will be :

char function(input)
char Alist={A-Z, a-z, 0-9, ...)
Scan each character of the input field.
If the input character falls in the Alist, accept the character else ignore it.

Make a new output field ignoring special characters not falling in Alist.

return(output field)

The returned field can be used in insert/update statement.