tag:blogger.com,1999:blog-495177919198719500.post6885198819151866999..comments2023-12-27T20:52:29.483-08:00Comments on Microsoft Sql Server Tutorials: Format Phone Numbers in SQL ServerSuprotim Agarwalhttp://www.blogger.com/profile/08349831623922214390noreply@blogger.comBlogger9125tag:blogger.com,1999:blog-495177919198719500.post-58942546102530935172014-01-29T09:43:30.448-08:002014-01-29T09:43:30.448-08:00It seems this query is only selecting three specif...It seems this query is only selecting three specific phone numbers and updating them. How do I select 15,000 phone numbers that are in the wrong format and correct the format on all of them? I can't put each one in my SELECT criteria...<br /> Anonymoushttps://www.blogger.com/profile/06754398554802927249noreply@blogger.comtag:blogger.com,1999:blog-495177919198719500.post-34359867770401649932014-01-26T23:56:40.176-08:002014-01-26T23:56:40.176-08:00I need the below format for displaying below:
if t...I need the below format for displaying below:<br />if the Length of the Phone no.is 10 then (123) 234-1234<br />If the len(Phno) >10 then<br />(123) 123-234 x1234455<br />Can you please help me on this.<br /><br />Thanks,<br />ValiAnonymoushttps://www.blogger.com/profile/00607017977904255327noreply@blogger.comtag:blogger.com,1999:blog-495177919198719500.post-76176288943570513262012-08-09T11:00:11.661-07:002012-08-09T11:00:11.661-07:00The STUFF function example is great thanks for the...The STUFF function example is great thanks for the helpAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-495177919198719500.post-40301388345388914232012-08-09T10:59:17.449-07:002012-08-09T10:59:17.449-07:00the stuff function is create thanks for the helpthe stuff function is create thanks for the helpAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-495177919198719500.post-34311979925012232842010-11-26T03:28:24.045-08:002010-11-26T03:28:24.045-08:00Here is the one that works for Indian format
sel...Here is the one that works for Indian format<br /><br /><br />select<br />phno, <br />case LEN(phno) <br />when 14 then '+'+STUFF(STUFF(phno,1,2,''),3,0,'-')<br />when 10 then phno<br />else 'Invalid phno'<br />end as formatted_phno <br />from<br />(<br />select<br />'00919443857375' as phno union all <br />select<br />'9447532568' as phno union all <br />select<br />'00919443858712' as phno union all <br />select<br />'2224353' as phno )<br />as tMadhivananhttp://www.beyondrelational.com/blogs/madhivanannoreply@blogger.comtag:blogger.com,1999:blog-495177919198719500.post-15466556004334838722010-11-26T01:47:44.353-08:002010-11-26T01:47:44.353-08:00brilliant article on Phone number formatting. Can ...brilliant article on Phone number formatting. Can you also show me how to do it for India phone number<br /><br />I have this 0091XXXXXXXXXX and I want to convert it to +91-XXXXXXXXXXAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-495177919198719500.post-29534007940222746252010-11-08T00:37:40.975-08:002010-11-08T00:37:40.975-08:00many many thank you for giving update table code. ...many many thank you for giving update table code. it worked perfectly<br /><br />RafaelAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-495177919198719500.post-47445390243509633962010-11-02T23:53:24.076-07:002010-11-02T23:53:24.076-07:00For updating a table, the code should be
update t...For updating a table, the code should be<br /><br />update table<br />set phno=<br />CASE LEN(phno)WHEN 11 THEN LEFT(phno,1)+ STUFF(STUFF(STUFF(phno,1,1,' ('),6,0,') '),11,0,'-')WHEN 10 THEN STUFF(STUFF(STUFF(phno,1,0,' ('),6,0,') '),11,0,'-')WHEN 7 THEN STUFF(phno,4,0,'-')ELSE phno END <br /><br /><br />I have given phno instead of 'Invalid format' so that your original data will be thereMadhivananhttp://www.beyondrelational.com/blogs/madhivanannoreply@blogger.comtag:blogger.com,1999:blog-495177919198719500.post-91710234643334211132010-11-02T21:32:41.932-07:002010-11-02T21:32:41.932-07:00i was searching for same solution. Can you show to...i was searching for same solution. Can you show to write update query for this. The number in column PhoneNos nvarchar is like 5551212 and I want to format this column with your algorithm. plz help<br /><br />RafaelAnonymousnoreply@blogger.com