fax , mobile , phone number reformat

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

fax , mobile , phone number reformat

Author

Topic

usafelix
Posting Yak Master

165 Posts

Posted - 2014-06-30 : 04:56:16

I have 100 tables and fields include mobile, fax, telephone. Company request us to restructure of customer their phone number, fax, mobile their pattern from 852-1234 5678 or (852) 1234-5678 or +852 1234 5678 to change in below.

Update of Between the number of gap from
HK mobile 852-xxxx xxxx
case 1
example : 852-1234 5678
result : +852-12345678

case 2
example : +852-1234-5678
result : +852-12345678

case 3
example : (852)-1234 5678
result : +852-12345678

case 4
example : (852) 1234 5678
result : +852-12345678

case 5
example : (852) 1234-5678
result : +852-12345678

Please give us advice how I can write a query update all tables ?

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-06-30 : 05:56:39

[code]
declare @vcTest as varchar(30) = '852-1234 5678'
--SET @vcTest ='+852-1234-5678'
--SET @vcTest =' (852)-1234 5678'
--SET @vcTest ='(852) 1234 5678'
SET @vcTest='(852) 1234-5678'

SELECT
'+' + STUFF(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@vcTest,' ' ,''),'-',''),'+',''),')',''),'(',''),4,0,'-')
[/code]

sabinWeb MCP

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-06-30 : 05:57:03

output:


+852-12345678

sabinWeb MCP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources