SQL Query

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?
LVL 1
PhilippeRenaudAsked:
Who is Participating?
 
dan_nealCommented:
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

0
 
dan_nealCommented:
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
0
 
PhilippeRenaudAuthor Commented:
I need to do in update of the table so I guess i just add:

update table set field as

(then your code) ?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.