SQL Query

Posted on 2009-04-20
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
    LVL 9

    Expert Comment

    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
    LVL 1

    Author Comment

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

    update table set field as

    (then your code) ?
    LVL 9

    Accepted Solution

    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

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    730 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

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now