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.
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 PhoneMobile0018672c-8549-4520-8fe2-663f0833691b +905324962947001ee16c-4dda-449b-a6c4-aedc0a1e9f47 +905326038476002674b1-4c71-48c8-99b3-3936b852f889 +905334296935Table 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 @tabselect '0018672c-8549-4520-8fe2-663f0833691b', +905324962947 union allselect '001ee16c-4dda-449b-a6c4-aedc0a1e9f47', +095326038476 union allselect '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 endFROM 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 endFROM Person t JOIN cte c ON c.uid_person = t.uid_personSELECT * 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 |
|
|
|
|
|
|
|