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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
2241 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
2241 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  
 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.16 seconds. Powered By: Snitz Forums 2000