[SQL] Interbase find these character (Code 13: Carriage Return (CR) Code 10: Line Feed (LF) )...

I am using interbase 2007 to connect and update information in a database.

and I am importing data from MSSQL database while working on interbase database.gdb file using delphi in a DBgrid

I am getting a line break in some field as "Cabezanellosa , Salamanca"

while i want it to be like "Cabezanellosa, Salamanca"

after copy the the break character and search that on [https://apps.timwhitlock.info/unicode/inspect] i got the character is UTF-8(10) and UTF-8(13) in decimal

I want to replace them with empty string.

i use a software made by my office to export the data to a text file. when any field have this line break that software stop operation and shows error

While working in MSSQL i can easily find and replace those character using this query

SELECT * FROM data WHERE BIRTH_PLACE LIKE '%' + CHAR(10) + '%' OR BIRTH_PLACE LIKE '%' + CHAR(13) + '%'


UPDATE data
SET BIRTH_PLACE = REPLACE(REPLACE(BIRTH_PLACE, CHAR(10), ''), CHAR(13), '')
WHERE BIRTH_PLACE LIKE '%' + CHAR(10) + '%' OR BIRTH_PLACE LIKE '%' + CHAR(13) + '%'


but this query is not working in interbase 2007. I also tried different AI generated query from multiple AI website as chatGPT, Claude, Copilot but none of them are working.

is there any way i can update these character to empty string using interbase 2007?

Continue reading...