Was trying to create a select statement that cleans up spaces when dealing with concatonating last name + suffix + comma + first name + middle name.

Example:
SELECT RTRIM(COALESCE('DOE' + ' ', '') +
                    COALESCE('III', '') + ', ' +
                    COALESCE('John' + ' ', '') +
                    COALESCE('E', '')) as "Legal Name"

Result = DOE III, John E - which is great.

However when there is no suffix
SELECT RTRIM(COALESCE('DOE' + ' ', '') +
                    COALESCE('', '') + ', ' +
                    COALESCE('John' + ' ', '') +
                    COALESCE('E', '')) as "Legal Name"

Result = DOE , John E - which has a space between DOE and the comma, which I don't want.

So this lead me to make this statement:

SELECT REPLACE(RTRIM(COALESCE('DOE' + ' ', '') +
                    COALESCE('', '') + ', ' +
                    COALESCE('John' + ' ', '') +
                    COALESCE('E', '')), ' , ', ', ') as "Legal Name"

Result =  ,

So either I'm misinterpreting what REPLACE does or there may be a problem with Replace function.

What I was expecting is

DOE, JOHN E


Ideas?

Lance

REPLACE works but I think you're misunderstanding what you're using as its arguments. If I'm interpreting it correctly it comes down to

REPLACE('DOE , John ,E' WITH ',' IN ',')

which will result in a ,


I created a function to do pretty much what you want. RCF stands for Reverse Catenate Fields. You can nest calls to achieve the desired result. For me the motivation was joining _Title, _Forename, _Surname. In your case - Surname, Forename, Initial - you would need

RCF(RCF(Initial,_Forename,' '),Surname,',')


CREATE FUNCTION "RCF" (IN "In1" VARCHAR COLLATE ANSI, IN "In2" VARCHAR COLLATE ANSI, IN "Separator " VARCHAR COLLATE ANSI)
RETURNS VARCHAR COLLATE ANSI
BEGIN
DECLARE Output VARCHAR;
SET Output = COALESCE(In2,'');
IF (In1 IS NOT NULL) AND (In1 <> '') THEN
IF (In2 IS NOT NULL) AND (In2 <> '') THEN
 SET Output = Output + Separator + In1;
 ELSE SET Output = In1;
END IF;
END IF;
RETURN Output;
END





Roy Lambert [Team Elevate]

Roy,

Thanks for the function code.  I'll try that out.  I'm trying to do LastName space Vintage comma space FirstName space MiddleInitial.

While I see the overloaded Replace function you mention, the other version that's defined in EDB as well as other SQL variants is REPLACE(ORIGINALSTRINGORFIELD, SEARCHVALUE, REPLACEVALUE).

So assuming I did my assembly of fields correctly, using REPLACE('DOE III , John E', ' , ', ', ') should find the comma space comma and change it to comma space, to eliminate the space between the last name (or vintage if one) and the comma.

Lance

Lance

>While I see the overloaded Replace function you mention, the other version that's defined in EDB as well as other SQL variants is REPLACE(ORIGINALSTRINGORFIELD, SEARCHVALUE, REPLACEVALUE).

Where have you seen that? The OLH gives REPLACE(<StringExpression> WITH <StringExpression> IN <StringExpression>).

To get what you're suggesting you'd have to alter REPLACE(string1 WITH string2 IN string3) to REPLACE(string2 WITH string3 IN string1)

What you seem to be quoting is (almost) Delphi's StringReplace function.

Roy Lambert [Team Elevate]

Roy,

The PDF SQL Manual for EDB. It looks like there is an overload of this function.

REPLACE(<StringExpression> WITH <StringExpression> IN <StringExpression>)
REPLACE(<StringExpression>, <StringExpression>,<StringExpression>)

<StringExpression> =
Type of:
CHARACTER|CHAR
CHARACTER VARYING|VARCHAR
GUID
CHARACTER LARGE OBJECT|CLOB

That being said, Roy, I changed the statement to the other way and it appears to work.

SELECT REPLACE(' , ' WITH ', ' IN
RTRIM(COALESCE('DOE' + ' ', '') +
                   COALESCE('', '') + ', ' +
                   COALESCE('John' + ' ', '') +
                   COALESCE('E', ''))) as "Legal Name"

Outputs  DOE, John E

SELECT REPLACE(' , ' WITH ', ' IN
RTRIM(COALESCE('DOE' + ' ', '') +
                   COALESCE('III', '') + ', ' +
                   COALESCE('John' + ' ', '') +
                   COALESCE('E', ''))) as "Legal Name"

Outputs  DOE III, John E

I also tried the above order using , for WITH and , for IN to match the overload and it works.

So I guess the question / problem needs to be refined to:

A) Should the manual / help file be updated to reflect REPLACE (string_pattern , string_replacement, string_expression) to better identify the order of what is expected in the overloaded function?

B)  Is the overloaded REPLACE not following the order of parameters that appears to be the defacto standard (I couldn't find in SQL-2003 Spec) with MSSQL, MySQL, PostgreSQL, DB2 and Oracle which is REPLACE ( string_expression , string_pattern , string_replacement )  ?

Obviously, now that I know the EDB order, I can deal with it, but I wonder if the overload function should have the parameters changed to follow SQL-2003 standard, since this will make it easier to port code from other DB's to EDB?

<<
A) Should the manual / help file be updated to reflect REPLACE (string_pattern , string_replacement, string_expression) to better identify the order of what is expected in the overloaded function?
>>

Sorry If i'm misunderstanding something here but the parameter order is the same for both function calls - there is no diffrerence AFAIK:
- REPLACE(<from> WITH <to>  IN <field/string>)
- REPLACE(<from>, <to>, <field/string>)

<<Obviously, now that I know the EDB order, I can deal with it, but I wonder if the overload function should have the parameters changed to follow SQL-2003 standard, since this will make it easier to port code from other DB's to EDB?>>

Lookign at the OLH the replace is listed as EDB extension so quite possibly it does not exist in sql 2003 at all. matching what other DBMS' are doing might be worhtwhile - this is up to Tim - and would break existing sql/

Raul

Lance

>REPLACE(<StringExpression> WITH <StringExpression> IN <StringExpression>)
>REPLACE(<StringExpression>, <StringExpression>,<StringExpression>)

As Raul indicates the difference here is replacing the WITH and IN by , (look at SUBSTRING and you'll see similar). It is not altering the order of the parameters.

Roy Lambert [Team Elevate]

By altering order of parameters, I'm meaning that the order of parameters for the Replace function using the overload version without the IN / WITH is not consistent with the "de facto standard" Replace function used by other DB's.

The Replace overload using IN / WITH is clear, however strictly an EDB variant.  The other method, while still a function that is not a full standard, is still a common one found and all seem to be supporting the common parameter order.

For me, while I can choose to use the EDB variant which is clear reading, I prefer to use the other as it allows me to move common SQL to EDB or the other way, when I need to.   I'm looking too at customers migrating code to EDB having to possibly do some extensive refactoring when they head scratch trying to find what is wrong with the results because the parameters are reversed.

It's just a small thing, but something I thought is important.  Just like nulls vs DBISAM behavior with nulls.  I prefer the standard nulls, yet also see where having EDB variant of option to support other behavior is reasonable.

Lance,

<< By altering order of parameters, I'm meaning that the order of parameters
for the Replace function using the overload version without the IN / WITH is
not consistent with the "de facto standard" Replace function used by other
DB's.  >>

The problem is that I can't change the order of the parameters and still
call it REPLACE without breaking existing SQL.  So, there's really not much
I can do about it, short of adding a new function name that just maps to the
same function (but with different parameter orderings), but I don't think
that's what you want.

Also, AFAIK, the REPLACE function is not mentioned in any SQL standards
docs, so technically it is an extensions in EDB.

--
Tim Young
Elevate Software
www.elevatesoft.com