• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3180
  • 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
 
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
 
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
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.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

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