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 2005 Forums
 Transact-SQL (2005)
 how do I replace the first character ?

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 44

but the solution below only returns 44 and instead of 44795123250

what is wrong ?
thanks


DECLARE @EACH_RECEPIENT VARCHAR(20),@defaultcountry VARCHAR(30)
SET @EACH_RECEPIENT = '0795123250'
SET @defaultcountry = '44'


if (left(@each_recepient,1) = 0)
begin
SET @EACH_RECEPIENT = (replace (left(@each_recepient,1), 0,@defaultcountry))
end

PRINT @EACH_RECEPIENT



Yes 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"
Go to Top of Page

mary_itohan
Posting Yak Master

191 Posts

Posted - 2008-09-10 : 18:16:00
variable

Yes O !
Go to Top of Page

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"
Go to Top of Page

mary_itohan
Posting Yak Master

191 Posts

Posted - 2008-09-10 : 18:17:13
You definitely are the next M$ MVP
thanks

_____________________


Yes O !
Go to Top of Page

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)
Go to Top of Page

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 0

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

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,
Go to Top of Page

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"
Go to Top of Page

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
Go to Top of Page

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 not

while 1 = 1
begin
UPDATE TOP (1000) Table1 SET Col1 = '44' + SUBSTRING(Col1, 2, 8000) WHERE Col1 LIKE '0%'
if @@rowcount = 0 break
end


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

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 not

while 1 = 1
begin
UPDATE TOP (1000) Table1 SET Col1 = '44' + SUBSTRING(Col1, 2, 8000) WHERE Col1 LIKE '0%'
if @@rowcount = 0 break
end


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.

Go to Top of Page

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,
Go to Top of Page

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)

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

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 describe
declare @s varchar(10)
set @s='0987234'
select 44 + SUBSTRING(@s, 2, 8000)



E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

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
Go to Top of Page

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"
Go to Top of Page

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)
---------------
44987234
987278[/code]

E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

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
Go to Top of Page

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"
Go to Top of Page

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 be



SET @phone:='';
SET @phone = '0987234';
SELECT concat('44', SUBSTRING(@phone,2,8000));


But as said, post your question at mysql forums

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
    Next Page

- Advertisement -