I need a generic ANSI SQL query(which would run on all RDBMS products) to solve the following problem:

 col1
 --------
 ab.cd
 mn.op
 pq.rs
 ef.gh

 I need a query which will replace the entries as shown in the following table:

 col1
---------
 abcd
 mnop
 pqrs
 efgh

Assume TABLE1 has the following rows.

COL1
--------------
ab.cd
mn.op
ef.gh
ef.gh
pq.rs


Use the following to get there...

select replace(COL1,".","") from TABLE1

Cheers!

-Vijay G

I doubt you will find a single function implemented across all RDBMS's, which will do what you want. Although the prior poster has pointed out the REPLACE function, this function is not part of the SQL-92 standard. It is implemented in SQL Server and others, though.

The SQL-92 standard does not define a REPLACE function. It defines a SUBSTRING function, a POSITION function, and a concatenate operator which can be combined to produce the equivalent of the SQL Server REPLACE function. Note that SQL Server doesn't implement POSITION; instead it implements the equivalent CHARINDEX function. Other RDBMS's no doubt implement other functions in other combinations.

No RDBMS implements the full SQL-92 standard, much less anything later. All available products implement the standard to varying degrees, some better than others, but all have taken various liberties and extended or changed their compliance in any number of ways...

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Jeff, Thanks for pointing out that.

Somehow it got in my mind that, this was just SQL server related.


Karjagis,

Here you go, the replacement to my previous post.

Using SUBSTRING() and CHARINDEX()
----------------------------------
select substring(col1,1,charindex('.',col1)-1) + substring(col1,charindex('.',col1),len(col1)-charindex('.',col1)+1) from test

Using LEFT(), RIGHT() and CHARINDEX()
-------------------------------------
select left(col1,charindex('.',col1)-1) + right(col1,len(col1)-charindex('.',col1)) from test

These are made using the STRING FUNCTIONS that are available with SQL server.

As Jeff mentioned, you could check with the STRING FUNCTIONS that are available with the corresponding PRODUCT that you use and slight modifitcation could be done to these above statements to get that work.

Cheers!

-Vijay G

Thanks to both of, Jeff and Vijay.

 Actually, I am working on a three - tier architecture based product and hence I am very particular about ANSI standard. I am afraid our current RDBMS (SQL Server) may get changed in future. In that case also, my middle tier should work. Anyway, my search is still on.. Let me see what happens...

Thanks again,
Sanket

Thanks to both of you, Jeff and Vijay.

 Actually, I am working on a three - tier architecture based product and hence I am very particular about ANSI standard. I am afraid our current RDBMS (SQL Server) may get changed in future. In that case also, my middle tier should work. Anyway, my search is still on.. Let me see what happens...

Thanks again,
Sanket