substring, replacing format

Question

hello i have row of data 1000 records, that i need to replace and substring in the format like below:

i have this format 004320487091 and i need to convert it to 0.432.487.091 any thought any helps please?

This question appears to be off-topic because the poster does not appear to have attempted any solution on his own — alroc, Nov 23 '14 at 13:51

score 0 · Accepted Answer · answered Nov 23 '14 at 08:38

0

If the string is always going to be same format as mentioned in your question then try this. Use STUFF Function to get the result.

select stuff(stuff(stuff(stuff('004320487091',3,0,'.'),7,1,'.'),11,0,'.'),1,1,'')

answered Nov 23 '14 at 08:38

Pரதீப்

91,748
19
131
172

score 0 · Answer 2 · answered Nov 23 '14 at 09:19

0

you can use concat plus substring like that

SELECT concat(substring('004320487091',2,1)
,'.',
(substring('004320487091',3,3))
,'.',
(substring('004320487091',7,3))
,'.',
(substring('004320487091',10,3)))

answered Nov 23 '14 at 09:19

Dudi Konfino

1,126
2
13
24

its says no concat function below error msg Msg 195, Level 15, State 10, Line 1 'concat' is not a recognized built-in function name. @Dudi Konfino – osama Nov 23 '14 at 10:25
i am using sql server 2012 and it works perfect – Dudi Konfino Nov 23 '14 at 10:32
am using 2008r2, might this new function not in sql2008 @Dudi Konfino – osama Nov 23 '14 at 10:42

substring, replacing format

2 Answers2