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

Transact-SQL (2000)

update query

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2004-10-29 : 07:00:57

Can someone help me with an update query.
What I want to do is check all phonenumber where country="us" and if the phonenumber starts with 1 then I want to delete just the one from the number and leave the rest of the phone number.

hgorijal
Constraint Violating Yak Guru

277 Posts

Posted - 2004-10-29 : 07:30:03

If char/varchar :
update <table>
set TEL_NUM =Right(TEL_NUM, len(TEL_NUM) -1)
where left(TEL_NUM, 1) = '1'

If int/number
update <table>
set TEL_NUM = Right(convert(varchar(15),TEL_NUM), len(convert(varchar(15),TEL_NUM)) -1)
where left(convert(varchar(15),TEL_NUM),1) = '1'

Hemanth Gorijala
BI Architect / DBA...(yuhoo!!! and now, an "Yak Master")

Exchange a Dollar, we still have ONE each.
Exchange an Idea, we have TWO each.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-10-29 : 07:30:34

This works if phonenumber is character data.


update tblX set
    phonenumber = substring(phonenumber,2,len(phonenumber)-1)
where
    country = 'us'
    and left(phonenumber) = '1'

rockmoose

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-10-29 : 07:32:09

Also I had a bug : left(phonenumber,1) = '1'

rockmoose

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-10-29 : 07:54:40

hgorijal,
you forgot the: where country="us" criteria.

better safe than sorry

rockmoose

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2004-10-31 : 03:49:11

When I do

update users set
    dayphonenumber = substring(dayphonenumber,2,len(dayphonenumber)-1)
where
    country = 'United States'
    and left(dayphonenumber,1) = '1'

I get the following error:
Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.
The statement has been terminated.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-10-31 : 04:06:50

Ok, you have a phonenumber which is 0 length.

update users set
    dayphonenumber = substring(dayphonenumber,2,len(dayphonenumber)-1)
where
    country = 'United States'
    and len(dayphonenumber) > 0
    and left(dayphonenumber,1) = '1'

rockmoose

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2004-10-31 : 04:14:27

great worked.
Now I need to change it to check all rows and if countrycode is the first letters in the dayphonenumber field then it should take out the country code?
Can you help me? Thanks

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-10-31 : 06:16:40

Please provide the code you have got so far and I will help you out

I dont quite understand :"it should take out the country code"

But essentially you have to compare something like:
....
countrycode = left(dayphonenumber,len(countrycode))
....

rockmoose

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-11-01 : 22:29:38

update users set
dayphonenumber = substring(dayphonenumber,2,len(dayphonenumber)-1)
where
country = 'United States'
and len(dayphonenumber) > 0
and left(dayphonenumber,1) = '1'
and patindex(countrycode + '%',dayphonenumber)>0

will this help?

--------------------
keeping it simple...

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2004-11-02 : 01:05:21

It worked for the US. I now want to change it to work for all countries. Meaning it should check countrycode and if the first letters of dayphone is the same as coutnry code it should delete those letters.

hgorijal
Constraint Violating Yak Guru

277 Posts

Posted - 2004-11-02 : 01:08:13

something this won't do?...

update users set
dayphonenumber = substring(dayphonenumber,2,len(dayphonenumber)-1)
where
--country = 'United States'and
len(dayphonenumber) > 0
and left(dayphonenumber,1) = '1'
and patindex(countrycode + '%',dayphonenumber)>0

Hemanth Gorijala
BI Architect / DBA (yuhoo!!! and now, an "Yak Master")

Exchange a Dollar, we still have ONE each.
Exchange an Idea, we have TWO each.

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2004-11-02 : 01:22:04

no because i'm talking about other country codes which are not one.
Also could be one number such as 1, 2 numbers such as 44 and 3 numbers like 972.

hgorijal
Constraint Violating Yak Guru

277 Posts

Posted - 2004-11-02 : 01:29:20

Now, In the column "countrycode" will contain 1 for US. right?
Then see if this works for US.

update users set
dayphonenumber = substring(dayphonenumber,2,len(dayphonenumber)-1)
where
country = 'United States' and 
len(dayphonenumber) > 0
--and left(dayphonenumber,1) = '1'
and patindex(countrycode + '%',dayphonenumber)>0

then this should work for all contries..

update users set
dayphonenumber = substring(dayphonenumber,2,len(dayphonenumber)-1)
where
--country = 'United States' and 
len(dayphonenumber) > 0
--and left(dayphonenumber,1) = '1'
and patindex(countrycode + '%',dayphonenumber)>0

Hemanth Gorijala
BI Architect / DBA (yuhoo!!! and now, an "Yak Master")

Exchange a Dollar, we still have ONE each.
Exchange an Idea, we have TWO each.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-11-02 : 01:33:30

quote:
Originally posted by esthera

no because i'm talking about other country codes which are not one.
Also could be one number such as 1, 2 numbers such as 44 and 3 numbers like 972.

lol, bottom line, just modify the script based on your needs. basically the logic is there...

--------------------
keeping it simple...

knight
Starting Member

13 Posts

Posted - 2004-11-05 : 08:16:18

Hi
I am new here, I like the above query.
same query, how about update to diff number instead of delete it?

Thank
knight

quote:
Originally posted by jen

quote:
Originally posted by esthera

no because i'm talking about other country codes which are not one.
Also could be one number such as 1, 2 numbers such as 44 and 3 numbers like 972.

lol, bottom line, just modify the script based on your needs. basically the logic is there...

--------------------
keeping it simple...

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-11-05 : 08:47:25

quote:
Originally posted by knight

Hi
I am new here, I like the above query.
same query, how about update to diff number instead of delete it?

Thank
knight

Indtroducing the concept of a translation table
oldprefix newprefix ( varchar cols )
1 3
56 77

update t set
phone = tr.newprefix + right(t.phone,len(phone)-len(tr.oldprefix))
from t
join translation tr on t.phone like(tr.oldprefix+'%')

Makes sense ?

rockmoose

knight
Starting Member

13 Posts

Posted - 2004-11-05 : 10:49:38

rockmoose

That good, but I do not want to create another table just want to change the last 4 digit of the phone number with a diff 4 digit number like '4444'

Thank
knight

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-11-05 : 11:00:48

If it is just a matter of one substitution like you describe:
change 4 last digits in phonenumber to '4444'.

update t set phonenumber = left(phonenumber,len(phonenumber)-4) + '4444'
-- of course you have to decide what to do when the phonenumber is less than 4 characters long.

What are you doing anyway ?
Do you have an actual case where you need to do something you have problems with ?

Post an example of what you are doing.

rockmoose

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-11-05 : 11:04:18

quote:
Originally posted by knight

rockmoose

That good, but I do not want to create another table just want to change the last 4 digit of the phone number with a diff 4 digit number like '4444'

Thank
knight

use a derived table if you do not want to create on physically.


select col1 from
( select '123' as col1 union select '456' ) as derived_table

rockmoose

knight
Starting Member

13 Posts

Posted - 2004-11-09 : 14:05:16

Hi,
I want to update knight name with kenny name. The following is my query and data.

Can any body help me with this?

data:
kkk - knight

update dbo.tblrfi_data set requestingoffice = right(requestingoffice,len(requestingoffice)-4) + 'kenny'
WHERE requestingoffice like '*knight*'

Thank,
Knight

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources