Replace string function

Hi

I have a table, some of the data in which is invalid. When it was loaded from an old system it came out with two spaces in between some words instead of one.

The column 'suppname' should have supplier names like 'ABC Ltd' but instead has 'ABC  Ltd' [two spaces]

How can I fix this?

LVL 3
eekjAsked:
Who is Participating?
 
Nick UpsonPrincipal Operations EngineerCommented:
I believe the easiest solution is to write your own udf to do it or use whatever client
you are comfortable with. I can be done in sql with a series of carefully written update
statements using substrings like this,

update table set suppname = substring before 2 spaces || substring after 2 spaces where
suppname has 2 spaces in a particular location, then repeat for every possible location
but this approach is so easy to get wrong I'd go with one of my first suggestions
0
 
Nick UpsonPrincipal Operations EngineerCommented:
Hi eekj,

which udf libraries do you have installed? what version of interbase or firebird are you using?

Cheers!
0
 
eekjAuthor Commented:

>which udf libraries do you have installed? what version of interbase or firebird are you using?
Not sure about udfs, whatever is standard... its firebird 1.5

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.