Update DB tbl using CTE - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Update DB tbl using CTE

Author

Topic

emmim44
Yak Posting Veteran

65 Posts

Posted - 2012-11-13 : 05:16:15

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

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-11-13 : 05:52:21

[code]
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
*/
[/code]

--
Chandu

emmim44
Yak Posting Veteran

65 Posts

Posted - 2012-11-13 : 08:02:48

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"

emmim44
Yak Posting Veteran

65 Posts

Posted - 2012-11-13 : 09:55:37

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"

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-11-19 : 01:55:45

quote:
Originally posted by emmim44

Never mind. Your solution was good. Thank you.

Welcome

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources