SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 how do I replace the first character ?
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

mary_itohan
Posting Yak Master

191 Posts

Posted - 09/10/2008 :  18:10:45  Show Profile  Reply with Quote
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

Sweden
30281 Posts

Posted - 09/10/2008 :  18:15:26  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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 - 09/10/2008 :  18:16:00  Show Profile  Reply with Quote
variable

Yes O !
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 09/10/2008 :  18:16:22  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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 - 09/10/2008 :  18:17:13  Show Profile  Reply with Quote
You definitely are the next M$ MVP
thanks

_____________________


Yes O !
Go to Top of Page

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 09/11/2008 :  08:29:49  Show Profile  Reply with Quote
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

India
22769 Posts

Posted - 09/11/2008 :  08:49:19  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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

United Kingdom
5 Posts

Posted - 10/01/2008 :  08:01:22  Show Profile  Visit aaronwood66's Homepage  Click to see aaronwood66's MSN Messenger address  Reply with Quote
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

Sweden
30281 Posts

Posted - 10/01/2008 :  08:08:08  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

Nigeria
2706 Posts

Posted - 10/01/2008 :  08:09:27  Show Profile  Reply with Quote
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

Sweden
30281 Posts

Posted - 10/01/2008 :  08:16:29  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

Nigeria
2706 Posts

Posted - 10/01/2008 :  08:24:27  Show Profile  Reply with Quote
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

United Kingdom
5 Posts

Posted - 10/01/2008 :  08:34:31  Show Profile  Visit aaronwood66's Homepage  Click to see aaronwood66's MSN Messenger address  Reply with Quote
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

India
22769 Posts

Posted - 10/01/2008 :  08:41:55  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote

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

Sweden
30281 Posts

Posted - 10/01/2008 :  08:54:17  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

United Kingdom
5 Posts

Posted - 10/01/2008 :  09:25:34  Show Profile  Visit aaronwood66's Homepage  Click to see aaronwood66's MSN Messenger address  Reply with Quote
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

Sweden
30281 Posts

Posted - 10/01/2008 :  10:01:18  Show Profile  Visit SwePeso's Homepage  Reply with Quote
What error message?



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

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 10/01/2008 :  10:09:10  Show Profile  Visit SwePeso's Homepage  Reply with Quote
DECLARE	@Sample VARCHAR(10)
SET @Sample = '0987234'
SELECT '44' + SUBSTRING(@Sample, 2, 8000)
SELECT	44 + SUBSTRING(@Sample, 2, 8000) 

(No columnname)
---------------
44987234
987278


E 12°55'05.63"
N 56°04'39.26"

Edited by - SwePeso on 10/01/2008 10:09:36
Go to Top of Page

aaronwood66
Starting Member

United Kingdom
5 Posts

Posted - 10/01/2008 :  10:50:25  Show Profile  Visit aaronwood66's Homepage  Click to see aaronwood66's MSN Messenger address  Reply with Quote
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

Sweden
30281 Posts

Posted - 10/01/2008 :  10:53:43  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

India
22769 Posts

Posted - 10/01/2008 :  11:12:13  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New 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.14 seconds. Powered By: Snitz Forums 2000