SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Update DB tbl using CTE
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

emmim44
Yak Posting Veteran

65 Posts

Posted - 11/13/2012 :  05:16:15  Show Profile  Reply with Quote
i all,

I need to update my person table column phonemobile column after doing some formatting operations. I have gotton some help to create a working version without my db tbl. My original table that i want to do update using cte is:

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


Edited by - emmim44 on 11/13/2012 05:17:49

bandi
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 11/13/2012 :  05:52:21  Show Profile  Reply with Quote

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
*/


--
Chandu
Go to Top of Page

emmim44
Yak Posting Veteran

65 Posts

Posted - 11/13/2012 :  08:02:48  Show Profile  Reply with Quote
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"
Go to Top of Page

emmim44
Yak Posting Veteran

65 Posts

Posted - 11/13/2012 :  09:55:37  Show Profile  Reply with Quote
Never mind. Your solution was good. Thank you.

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"

Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 11/19/2012 :  01:55:45  Show Profile  Reply with Quote
quote:
Originally posted by emmim44

Never mind. Your solution was good. Thank you.



Welcome

--
Chandu
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000