SQL Query

Posted on 2009-04-20
Medium Priority
Last Modified: 2012-05-06
Hello, I need help to modify some data inside a table.

I have text in a lot of rows in that format: 1234567890

I need to insert inside each field dots.   I need it to be:   1234.56.78.90

I have some alrady in my table as that format (with dots) wich is fine and I do not need to change them.

for the rest.. i need a modification.  How could I do that inside a SQL query ? any idea?
Question by:PhilippeRenaud
  • 2

Expert Comment

ID: 24187875
select case when charindex('.',field) > 0 then field else substring(field,1,4) + '.' + substring(field,5,2) + '.' + substring(field,7,2) + '.' + substring(field,9,2) end as formatted_text from table

Author Comment

ID: 24187920
I need to do in update of the table so I guess i just add:

update table set field as

(then your code) ?

Accepted Solution

dan_neal earned 2000 total points
ID: 24187956
Well in an update you could do.
I would recommend verifying the substring reformatting looks correct before applying with an update.  The where claus will exclude those already formatted as desired.

Update table
   set field = substring(field,1,4) + '.' + substring(field,5,2) + '.' + substring(field,7,2) + '.' + substring(field,9,2)
where NOT(field like '%.%')

Open in new window


Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question