Author |
Topic |
mary_itohan
Posting Yak Master
191 Posts |
Posted - 2008-09-10 : 18:10:45
|
Hello,am trying to replace the first character (if 0 ) with 44but the solution below only returns 44 and instead of 44795123250what is wrong ?thanksDECLARE @EACH_RECEPIENT VARCHAR(20),@defaultcountry VARCHAR(30)SET @EACH_RECEPIENT = '0795123250'SET @defaultcountry = '44'if (left(@each_recepient,1) = 0)beginSET @EACH_RECEPIENT = (replace (left(@each_recepient,1), 0,@defaultcountry))endPRINT @EACH_RECEPIENTYes O ! |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-09-10 : 18:15:26
|
Is this done for a table or just a variable? E 12°55'05.63"N 56°04'39.26" |
|
|
mary_itohan
Posting Yak Master
191 Posts |
Posted - 2008-09-10 : 18:16:00
|
variableYes O ! |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-09-10 : 18:16:22
|
if @each_recepient like '0%'SET @EACH_RECEPIENT = Stuff(@each_recepient, 1, 1, @defaultcountry) E 12°55'05.63"N 56°04'39.26" |
|
|
mary_itohan
Posting Yak Master
191 Posts |
Posted - 2008-09-10 : 18:17:13
|
You definitely are the next M$ MVPthanks_____________________Yes O ! |
|
|
dineshrajan_it
Posting Yak Master
217 Posts |
Posted - 2008-09-11 : 08:29:49
|
DECLARE @EACH_RECEPIENT VARCHAR(20),@defaultcountry VARCHAR(30)SET @EACH_RECEPIENT = '0795123251'SET @defaultcountry = '44'SELECT @defaultcountry + RIGHT(@EACH_RECEPIENT,LEN(@EACH_RECEPIENT)-1) |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2008-09-11 : 08:49:19
|
quote: Originally posted by dineshrajan_it DECLARE @EACH_RECEPIENT VARCHAR(20),@defaultcountry VARCHAR(30)SET @EACH_RECEPIENT = '0795123251'SET @defaultcountry = '44'SELECT @defaultcountry + RIGHT(@EACH_RECEPIENT,LEN(@EACH_RECEPIENT)-1)
You did not validate that first character should be 0MadhivananFailing to plan is Planning to fail |
|
|
aaronwood66
Starting Member
5 Posts |
Posted - 2008-10-01 : 08:01:22
|
I have a similar problem I hope someone can help me with. I have about 1500 records that I need to change permentantly in a database -mobile numbers that start with 0 need to start with 44. I have been looking on every forum I can find but cannot find an answer. If anyone has any suggestions, I would be very very grateful. I am quite new to SQL so go easy on me!Cheers, |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-10-01 : 08:08:08
|
Don't hijack topics.Start your own!UPDATE Table1 SET Col1 = '44' + SUBSTRING(Col1, 2, 8000) WHERE Col1 LIKE '0%' E 12°55'05.63"N 56°04'39.26" |
|
|
afrika
Master Smack Fu Yak Hacker
2706 Posts |
Posted - 2008-10-01 : 08:09:27
|
We also work with mobile numbers and have a few millions.I would advice you run a loop to update your records based on an indentity column |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-10-01 : 08:16:29
|
You mean like one record update at a time?why notwhile 1 = 1beginUPDATE TOP (1000) Table1 SET Col1 = '44' + SUBSTRING(Col1, 2, 8000) WHERE Col1 LIKE '0%'if @@rowcount = 0 breakend E 12°55'05.63"N 56°04'39.26" |
|
|
afrika
Master Smack Fu Yak Hacker
2706 Posts |
Posted - 2008-10-01 : 08:24:27
|
quote: Originally posted by Peso You mean like one record update at a time?why notwhile 1 = 1beginUPDATE TOP (1000) Table1 SET Col1 = '44' + SUBSTRING(Col1, 2, 8000) WHERE Col1 LIKE '0%'if @@rowcount = 0 breakend E 12°55'05.63"N 56°04'39.26"
Brilliant code Peter.I never saw it that way, We have a mixture of different country number formats. |
|
|
aaronwood66
Starting Member
5 Posts |
Posted - 2008-10-01 : 08:34:31
|
Apologies for hijacking this forum, but thank you all for your quick response.I have seen similar code to yours, Peso, but something weird happens. It removes the first 0 but then adds the number 44 on to the phone number, sort of like a sum. My column type is VARCHAR so I don't know why its doing this.Thanks, |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2008-10-01 : 08:41:55
|
declare @s varchar(10)set @s='0987234'select '44'+SUBSTRING(@s, 2, 8000)MadhivananFailing to plan is Planning to fail |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-10-01 : 08:54:17
|
As Madhi demonstrated, it works great.However, if the '44' is written without single quotes, you get the result you describedeclare @s varchar(10)set @s='0987234'select 44 + SUBSTRING(@s, 2, 8000) E 12°55'05.63"N 56°04'39.26" |
|
|
aaronwood66
Starting Member
5 Posts |
Posted - 2008-10-01 : 09:25:34
|
When I tried your code, Peso:UPDATE students SET phone = '44' + SUBSTRING(phone, 2, 8000) WHERE phone LIKE '0%'- thats when I had the 44 adding onto the number like a sum.When I tried the this code instead:declare @s varchar(10)set @s='0987234'select '44'+SUBSTRING(@s, 2, 8000)- I got an error message. Am I entering this correct? Just these 3 lines on their own?Aaron |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-10-01 : 10:01:18
|
What error message? E 12°55'05.63"N 56°04'39.26" |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-10-01 : 10:09:10
|
[code]DECLARE @Sample VARCHAR(10)SET @Sample = '0987234'SELECT '44' + SUBSTRING(@Sample, 2, 8000)SELECT 44 + SUBSTRING(@Sample, 2, 8000) (No columnname)---------------44987234987278[/code] E 12°55'05.63"N 56°04'39.26" |
|
|
aaronwood66
Starting Member
5 Posts |
Posted - 2008-10-01 : 10:50:25
|
The error message I got said:#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE @phone VARCHAR(10)SET @phone = '0987234'SELECT '44' + SUBSTRING(@pho' at line 1 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-10-01 : 10:53:43
|
MySQL? You have missed this is a Microsoft SQL Server forum? E 12°55'05.63"N 56°04'39.26" |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2008-10-01 : 11:12:13
|
quote: Originally posted by aaronwood66 The error message I got said:#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE @phone VARCHAR(10)SET @phone = '0987234'SELECT '44' + SUBSTRING(@pho' at line 1
In Mysql, it should beSET @phone:='';SET @phone = '0987234';SELECT concat('44', SUBSTRING(@phone,2,8000));But as said, post your question at mysql forumsMadhivananFailing to plan is Planning to fail |
|
|
Next Page
|