Delete extra spaces?

[Former Member]

Hi all,

I've added two UDF's to a contact of a business partner:

- U_voornaam

Front name with 150 characters.

- U_achternaam

This is the family name, also 150 characters.

Now I've built a FMS for the field OCPR.Name (50 characters):

SELECT $[OCPR.U_voornaam] + ' ' + $[OCPR.U_achternaam]

The problem is now that when a run this FMS, SBO says there are too many characters, because U_voornaam starts with pos0 with filling the field and U_achternaam starts with pos149 with filling the field.

An example:

U_voornaam = Lars

U_achternaam = Pelzer

The result: "Lars{about 290 spaces}Pelzer"

Is there a possibility to solve this problem?

Can de spaces be removed?

Thank you in advance!

Greetz

[Former Member · Accepted solution]

use replace function as replace(x, ' ', '') or ltrim rtrim - I think that in zour case is RTRIM suitable as

SELECT $[rtrim(OCPR.U_voornaam)] + ' ' + $[RTRIM(OCPR.U_achternaam)]

[Former Member · Accepted solution]

Hi Petr,

It does work, but now I have sometime two parts en sometimes three parts. I only want to take the middle part if it's filled.

When I only do this:

SELECT RTRIM($[OCPR.U_voornaam]) + ' ' + RTRIM($[OCPR.U_tussenvoegsels]) + ' ' + RTRIM($[OCPR.U_achternaam]

I get double spaces when the middle part isn't filled, so I came up with this:

SELECT 
(CASE WHEN T0.U_tussenvoegsels IS NOT NULL 
THEN (RTRIM($[OCPR.U_voornaam]) + ' ' + RTRIM($[OCPR.U_tussenvoegsels]) + ' ' + RTRIM($[OCPR.U_achternaam]) ELSE (RTRIM($[OCPR.U_voornaam]) + ' ' + LTRIM($[OCPR.U_achternaam]) END) 
FROM OCPR T0

Can you help me with this final step?

Thank you in advance!

Greetz

Lars

[Former Member · Accepted solution]

use

SELECT RTRIM($[OCPR.U_voornaam]) + ' ' + RTRIM(coalesce($[OCPR.U_tussenvoegsels], '')) + ' ' + RTRIM($[OCPR.U_achternaam]

or

SELECT replace(RTRIM($[OCPR.U_voornaam]) + ' ' + RTRIM(coalesce($[OCPR.U_tussenvoegsels], '')) + ' ' + RTRIM($[OCPR.U_achternaam], ' ', ' ') - replacing double space with one space

Edited by: Petr Verner on Jul 16, 2008 5:32 PM

[Former Member · Accepted solution]

This did the trick:

SELECT replace(RTRIM($[OCPR.U_voornaam]) + ' ' + RTRIM(coalesce($[OCPR.U_tussenvoegsels], '')) + ' ' + RTRIM($[OCPR.U_achternaam]), '  ', ' ')

Thank you very much for your time and effort!

Greetz

Lars