Data:
uid_person	PhoneMobile
0018672c-8549-4520-8fe2-663f0833691b	+905324962947
001ee16c-4dda-449b-a6c4-aedc0a1e9f47	+905326038476
002674b1-4c71-48c8-99b3-3936b852f889	+905334296935

Table structure:
	Select	uid_person,PhoneMobile from Person 
	where PhoneMobile is not null
	and IsInActive = 0
	and not isnull(PersonnelNumber,'') = ''

my formatting script that i want to convert to a cte to update phonemobile column of person tbl.
declare @thestring varchar(50) 
set @thestring = '5548619319' 
declare @final varchar(50) 
declare @newN varchar(50) 
set @final = '' 
set @newN = '' 
select @final = @final + x.thenum 
	from 
	( 
		select substring(@thestring, number, 1) as thenum, number 
		from master..spt_values 
		where substring(@thestring, number, 1) like '[0-9]' and type='P'
	) x 
order by x.number 
set @final = LTRIM(RTRIM (@final))
--print len (@final ) 
--print 'Before: '+@final 
Select @newN =
	case  
		when Substring(@final,1,2) ='90' and len (@final ) = 12 then Substring(@final,2,11)
		when Substring(@final,1,1) ='9' and len (@final ) = 11 then  '0' + Substring(@final,2,10)
		when Substring(@final,1,1) ='0' and len (@final ) = 11 then Substring(@final,1,11)
		when Substring(@final,1,1) <> '0' and len (@final ) = 10 then '0'+ @final
		Else
		@final
	end
--print 'After: '+@newN

declare @tab table (uid_person	varchar(100), PhoneMobile varchar(15))
insert into @tab
select '0018672c-8549-4520-8fe2-663f0833691b',	+905324962947 union all
select '001ee16c-4dda-449b-a6c4-aedc0a1e9f47',	+095326038476 union all
select '002674b1-4c71-48c8-99b3-3936b852f889',	05334296935

;with cte 
as (Select	uid_person,PhoneMobile from Person 
	where PhoneMobile is not null
	and IsInActive = 0
	and not isnull(PersonnelNumber,'') = ''
	)
Select uid_person, PhoneMobile,
	case  
		when Substring(PhoneMobile,1,2) ='90' and len (PhoneMobile ) = 12 then Substring(PhoneMobile,2,11)
		when Substring(PhoneMobile,1,1) ='9' and len (PhoneMobile ) = 11 then  '0' + Substring(PhoneMobile,2,10)
		when Substring(PhoneMobile,1,1) ='0' and len (PhoneMobile ) = 11 then Substring(PhoneMobile,1,11)
		when Substring(PhoneMobile,1,1) <> '0' and len (PhoneMobile ) = 10 then '0'+ PhoneMobile
		Else
		PhoneMobile
	end
FROM cte
/*

;with cte 
as (Select	uid_person,PhoneMobile from Person 
	where PhoneMobile is not null
	and IsInActive = 0
	and not isnull(PersonnelNumber,'') = ''
)
update t
 SET PhoneMobile =	case  
		when Substring(t.PhoneMobile,1,2) ='90' and len (t.PhoneMobile ) = 12 then Substring(t.PhoneMobile,2,11)
		when Substring(t.PhoneMobile,1,1) ='9' and len (t.PhoneMobile ) = 11 then  '0' + Substring(t.PhoneMobile,2,10)
		when Substring(t.PhoneMobile,1,1) ='0' and len (t.PhoneMobile ) = 11 then Substring(t.PhoneMobile,1,11)
		when Substring(t.PhoneMobile,1,1) <> '0' and len (t.PhoneMobile ) = 10 then '0'+ t.PhoneMobile
		Else
		t.PhoneMobile
	end
FROM Person t JOIN cte c ON c.uid_person = t.uid_person

SELECT * FROM Person
*/

		select substring(@thestring, number, 1) as thenum, number 
		from master..spt_values 
		where substring(@thestring, number, 1) like '[0-9]' and type='P'

quote:

---

Originally posted by emmim44

You are skipping this part...

		select substring(@thestring, number, 1) as thenum, number 
		from master..spt_values 
		where substring(@thestring, number, 1) like '[0-9]' and type='P'

Before case statement, I need remove all non numeric characters such as "space, +, - ,(,)...etc"

---

quote:

---

Originally posted by emmim44

Never mind. Your solution was good. Thank you.

---