Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3154
  • Last Modified:

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?

0
eekj
Asked:
eekj
  • 2
1 Solution
 
NickUpsonSenior Network 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
 
NickUpsonSenior Network 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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now