cancel
Showing results for 
Search instead for 
Did you mean: 

Removing special characters

ash_19
Occasional Visitor

Removing special characters

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
4 REPLIES
Sanjay Kumar Suri
Honored Contributor

Re: Removing special characters

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.

A rigid mind is very sure, but often wrong. A flexible mind is generally unsure, but often right.
Rajeev Shukla
Honored Contributor

Re: Removing special characters

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
Honored Contributor

Re: Removing special characters

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

-- Graham
Computers make it easier to do a lot of things, but most of the things they make it easier to do don't need to be done.
Sanjay Kumar Suri
Honored Contributor

Re: Removing special characters

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.

A rigid mind is very sure, but often wrong. A flexible mind is generally unsure, but often right.