Remove all spaces within a string? *Banging my head against the wall*

[cabaynes]

I have a string value that has a few thousand records, and many of them are multi-word items, so they contain spaces. I need to get rid of all of the spaces.

I am aware of the TRIM function, but that is only good for leading and trailing spaces, not ones in the middle of the string.

The REPLACE function does not seem to be working for me, nor does SUBSTITUTE, or COLLAPSE. I attempted to cast the string to a varchar, then use REPLACE, but that does not seem to work either.

I've been scouring the internet for hours trying to figure this out. Any ideas would be greatly appreciated!

Thank you
Charles

[Lynn]

What do you mean when you say "does not seem to work"? Error? Unexpected result?

Also, what database are you using?

The REPLACE function works fine for me

Code Select Expand

REPLACE([MyField],' ', '')

There is also a TRANSLATE function, but that doesn't allow me to specify an empty string. I am on DB2.

Of course the ideal solution is to do this on the database side.

[cabaynes]

Yes, I get an error. We are using Teradata database, so I don't think the REPLACE function is available in anything other than Oracle.

[CognosPaul]

How much control do you have over the database? If none, is the DBA open to installing some UDFs?

If you can, download and import the following UDFs:
http://downloads.teradata.com/download/extensibility/teradata-udfs-for-popular-oracle-functions

I haven't worked with UDFs in a long time, and you may need to import them into the FM before you can use them.

[blom0344]

I haven't worked with UDFs in a long time, and you may need to import them into the FM before you can use them.

Yes, importing them into the FM model is required, but you also need to make sure that execution rights are established. At least on SQL server having read rights on the database is not enough when you start using UDF's. Pretty sure this goes for other RDBMS

[cabaynes]

How much control do you have over the database? If none, is the DBA open to installing some UDFs?

If you can, download and import the following UDFs:
http://downloads.teradata.com/download/extensibility/teradata-udfs-for-popular-oracle-functions

I haven't worked with UDFs in a long time, and you may need to import them into the FM before you can use them.

Thanks for help. I have zero control over the database, but will speak to my DBA to see if we can get these features. Seems like "oreplace" is exactly what I'm looking for.

Regards!