Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
|
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old April 5th, 2004, 04:31 AM
Registered User
 
Join Date: Apr 2004
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default How to remove the dot and concatenate

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
 
Old April 5th, 2004, 06:59 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
 
Old April 5th, 2004, 12:00 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
 
Old April 6th, 2004, 04:30 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
 
Old April 6th, 2004, 05:32 AM
Registered User
 
Join Date: Apr 2004
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old April 6th, 2004, 05:33 AM
Registered User
 
Join Date: Apr 2004
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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






Similar Threads
Thread Thread Starter Forum Replies Last Post
concatenate records davehodges Access 5 November 1st, 2007 10:13 AM
Hyperlink (Concatenate) JEHalm Excel VBA 1 January 11th, 2006 01:09 PM
Argh! Why does this concatenate? interrupt Javascript How-To 1 August 13th, 2005 08:44 AM
concatenate with characters lily611 ADO.NET 1 July 21st, 2004 08:10 AM
Concatenate 2 variables into one? cpopham Access VBA 2 December 9th, 2003 10:27 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.