Update Query - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Update Query

Author

Topic

yatesy87
Starting Member

8 Posts

Posted - 2005-11-23 : 11:58:53

I was wondering if somebody could help. Im fairly new to using SQL but I have been given a task where by I just dont know what function to use.

We have phone numbers but alot of them they dont have the area code at the beginning so in the table instead of looking like 01744455655 it looks like 455655.

What i need to do is create a query where by if that number (455655) is associated with the post code WA9 then the Query would add 01744 to the beginning of that phone number. The post code for the phone numbers are in the table along with it so that information is their i just need to know how to edit the beginning of the number field.

Any help is much appreciated

Thanks

nr
SQLTeam MVY

12543 Posts

Posted - 2005-11-23 : 12:12:34

update tbl
set PhoneNumber = '01744' + PhoneNumber
where left(PhoneNumber,1) <> '0'
and PostCode like 'WA9%'

Better if you have the postcodes and dialling codes in a table then

update tbl
set PhoneNumber = t2.DIallingCode + t1.PhoneNumber
from tbl t1
join codes t2
on t2.PostCode like t1.Postcode + '%'
where left(t1.PhoneNumber,1) <> '0'

be carefull of postcodes that are substrings of others and phone numbers that have the dialling codes but without the 0.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

X002548
Not Just a Number

15586 Posts

Posted - 2005-11-23 : 12:27:08

Something tells me there's a code table in here...read the hint link in my sig and supply more details


USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(phone varchar(11), post_cd varchar(10))
GO

INSERT INTO myTable99(phone, post_cd)
SELECT '455655', 'WA9' UNION ALL
SELECT '01744455666', 'WA9'
GO

SELECT CASE WHEN LEN(phone) = 6 
	    THEN '01744' + phone 
	    ELSE phone 
	END AS phone
  FROM myTable99
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources