Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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 ?
 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
30421 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
30421 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
22864 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
30421 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
30421 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
22864 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
30421 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
30421 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
30421 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
30421 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
22864 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
 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.11 seconds. Powered By: Snitz Forums 2000