replacesql-updateinformix

Is it possible to write an update statement using replace where we just know the start and end of a string that we want to cut

I have a table called service and a column called xml. In one of the records, the xml value is:

SELECT xml 
FROM service 
WHERE xml LIKE '%Password%;

Output

<UseUDS>true</UseUDS><UseUserCredential>true</UseUserCredential>**<DN>amrit</DN><Password>amrit</Password>**<SearchContext1></SearchContext1><SearchContext2></SearchContext2><SearchContext3></SearchContext3><RecursiveSearch1>true</RecursiveSearch1><SearchTimeout>5</SearchTimeout><BaseFilter></BaseFilter><PredictiveSearchFilter></PredictiveSearchFilter>

I want to write an update statement using replace function. It should remove the <DN>amrit</DN><Password>amrit</Password> from the column called xml and keep the rest of its content.

Do note that amrit can be anything!

I tried using wild characters, but it didn't help:

UPDATE service 
SET xml = REPLACE(xml,'<DN>%</DN><Password>%</Password>','')
WHERE xml LIKE '%Password%;

Is it possible to write an UPDATE statement using REPLACE() where we just know the start and end of a string that we want to cut?

Solution

  • I am going to assume you are working with a relatively newer version of Informix (like 11.70 or 12.10).
    Here is one way to achieve what you are looking to do, provided your xml string is going to remain fairly straight forward as you've shown:
    update service set xml = concat(substring_index(xml, "<DN>", 1), substring_index(xml, "</Password>", -1)) where xml like '%Password%' ;
    Here is a complete list of string manipulation functions available in Informix https://www.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.sqls.doc/ids_sqs_1554.htm#ids_sqs_1554