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
 Old Forums
 CLOSED - General SQL Server
 Updating e-mail domains
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

INFLICT
Starting Member

15 Posts

Posted - 03/08/2002 :  11:38:25  Show Profile
Hello everyone,
I am having a big problem at work. I need to update the database. What I have to do is update a few hundred records to reflect a domain name change. Example ME@INFLICT.COM needs to be ME@sqlteam.com. I need a select statement that will carry over the user name and change the domain after the "@". My boss told me I can do this using a Charindex ? command but I am not familiar with this. Please someone help, I'm at work now and this must be done ASAP.

Thanking you in advance.

Jay99
Constraint Violating Yak Guru

USA
468 Posts

Posted - 03/08/2002 :  11:44:24  Show Profile
Take a look at 'replace' in BOL


quote:

Replaces all occurrences of the second given string expression in the first string expression with a third expression.

REPLACE('string_expression1', 'string_expression2', 'string_expression3')



Jay
Go to Top of Page

INFLICT
Starting Member

15 Posts

Posted - 03/08/2002 :  11:53:15  Show Profile
BOL? I don't understand what that is? I tried running a replace statement something like UPDATE (TABLENAME) REPLACE ('ME@INFLICT.COM','INFLICT.COM','SQLTEAM.COM') where homeemail like '%INFLICT.COM'

and it replaced all the e-mail addresses with only 'SQLteam.com' it didn't carry over everything before the @ sign. Lucky this was not the live database or I might of been fired.

Go to Top of Page

INFLICT
Starting Member

15 Posts

Posted - 03/08/2002 :  12:05:06  Show Profile
Please someone can you give me an exact statement? My boss is waiting for this to be done. Since yesterday, please I need help quick!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Go to Top of Page

robvolk
Most Valuable Yak

USA
15657 Posts

Posted - 03/08/2002 :  12:08:07  Show Profile  Visit robvolk's Homepage
BOL is shorthand for Books Online, which is the documentation that comes with SQL Server. It has details on the syntax for SQL statements. You were very close on your UPDATE statement:

UPDATE TABLENAME
SET homeemail=REPLACE(homeemail,'INFLICT.COM','SQLTEAM.COM')
WHERE homeemail LIKE '%INFLICT.COM'


I strongly recommend that you spend as much time as possible learning about SQL before you find yourself under the gun to get the job done. Look through BOL at the examples for UPDATE (and other SQL statements) so that you're familiar with them.

Edited by - robvolk on 03/08/2002 12:10:08
Go to Top of Page

Jay99
Constraint Violating Yak Guru

USA
468 Posts

Posted - 03/08/2002 :  12:11:44  Show Profile
quote:

...give me an exact statement? My boss is waiting for this to be done. Since yesterday, please I need help quick!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!





Jay
Go to Top of Page

INFLICT
Starting Member

15 Posts

Posted - 03/08/2002 :  12:14:44  Show Profile
I appreciate your help I have read up a little but I didn't find anything about this subject in any of the books I've been reading. I have Guru's guide to transact and Mastering SQL 2000.

Go to Top of Page

robvolk
Most Valuable Yak

USA
15657 Posts

Posted - 03/08/2002 :  12:15:17  Show Profile  Visit robvolk's Homepage
We'll just send a bill to his company for the consulting time. I'm sure his boss will appreciate it tremendously.

What would be a good rate? $500 an hour? Anyone?

Seriously, if you have those books and you DON'T use Books Online as well, you wasted your money on them. UPDATE is a very basic operation.

Edited by - robvolk on 03/08/2002 12:17:08
Go to Top of Page

INFLICT
Starting Member

15 Posts

Posted - 03/08/2002 :  12:33:17  Show Profile
Well my boss wants to convert me to a dba he says everyone on his team (IT) makes more money than me and so I need to make more money and I could do it by converting to a dba. Any suggestions on great books to start me on my way. I know some SQL but mostly the basics.

Go to Top of Page

Jay99
Constraint Violating Yak Guru

USA
468 Posts

Posted - 03/08/2002 :  13:12:01  Show Profile
Here are a couple things to help you down the path of becoming a dba.


  • The first step is not to go out and buy more books. You already have 2 (and one from microsoft you didn't even know about) and they aren't helping you yet.

  • Learn to manage your boss's expectations. Don't get yourself into a jam and then demand to be saved by someone on the internet who you don't even know.

  • When someone says 'Charindex' or 'Replace' or even 'BOL', if you don't know what that is, look it up. A search on this forum alone for the string 'What is BOL' will point you to http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=8509 where someone clarified. (By the way, BOL is a DBAs first resource, I usually walk into a client site completely empty handed, because I know if they have SQL Server they will have BOL and if they have BOL I can find out anything I don't already know)

  • Never run a command into SQL Server that you don't know what it is going to do. -"I tried running a replace statement...it didn't carry over everything before the @ sign."

  • Especially never run in any command you don't know how to undo, if you have to.

  • Never attempt to even slightly imply that something you did is somehow my (or anyone else's) fault. - "Lucky this was not the live database or I might of been fired."

  • Don't post to a forum asking for help (or more clarification) and then repost 12 minutes later screaming about hurry up. Some of us (although not evident by my post here) have better things to do/discuss.

  • Realize that people generally don't want to give you "an exact statement" without knowing first that you have done everything in your power to solve the problem yourself.

  • Realize that you don't know "mostly the basics" at all.



This is not meant to be a flame, but rather some constructive critism. You came here looking for someone to do your work for you. I replied with a relatively clear suggestion how you might go about solving your problem. Then you started scream about how 'the service in this place' is too slow and nearly got you fired. Check yourself, son, because I am sure that if you think about it, you will see my point.


Jay
Go to Top of Page

JustinBigelow
SQL Gigolo

USA
1157 Posts

Posted - 03/08/2002 :  13:13:18  Show Profile
quote:

Any suggestions on great books to start me on my way. I know some SQL but mostly the basics.



Not to beat a dead horse but...Books On Line!!!! You could go a long way on that alone. It will take you from beggining to advanced with examples for almost any situation. It has a great search tool and index. If you want a book more geared toward teaching as opposed to pure reference then I found Rob Viereras book "Profesional SQL Server 7 Programming" to be quite good.

m2c,
Justin

Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

Australia
4970 Posts

Posted - 03/11/2002 :  19:50:03  Show Profile  Visit Merkin's Homepage
Jay

Great post. I think that might need to become an article.

Damian
Go to Top of Page

rrb
SQLTeam Poet Laureate

Australia
1479 Posts

Posted - 03/11/2002 :  21:35:42  Show Profile
quote:

Lucky this was not the live database or I might of been fired.



When I was a musician, people used to tell me "don't give up your day-job"...but perhaps in your case...

Why don't you go tell your boss where he can get off? - I hear that also solves the problem.

Be sure and post us back when you've tried it...

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"
Go to Top of Page

rrb
SQLTeam Poet Laureate

Australia
1479 Posts

Posted - 03/11/2002 :  21:37:29  Show Profile
PS - agree with Damian.


PPS - well guess where http://www.inflict.com leads!

Edited by - rrb on 03/11/2002 21:40:10

Edited by - rrb on 03/11/2002 21:41:11
Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

Australia
4970 Posts

Posted - 03/12/2002 :  18:21:13  Show Profile  Visit Merkin's Homepage
quote:


When I was a musician, people used to tell me "don't give up your day-job"


Bloody musicians....they are everywhere!

Damian
Go to Top of Page

rrb
SQLTeam Poet Laureate

Australia
1479 Posts

Posted - 03/12/2002 :  18:25:54  Show Profile
quote:

Bloody musicians....they are everywhere!



Bloody musicians who say "bloody musicians...they're everywhere" - now they are everywhere!

Maybe we could start a club of musos who realised they could actually make a very respectable living and have fun - as long as they gave up the music

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"
Go to Top of Page

joldham
Wiseass Yak Posting Master

USA
300 Posts

Posted - 03/12/2002 :  19:34:25  Show Profile
I suggest you tell your boss that if he intends for you to become a DBA and get the salary a DBA deserves, that he should pay for you to attend a DBA course at your local college or Vo-Tech. I learned everything I know on the internet (SQL) and in books online (Advance SQL). I fell I have a pretty good handle on these things and I just started two weeks ago to take DBA courses. You must understand database theory in order to be able to perform your job.

You must realize and make your boss realize that learning the duties of a DBA does not mean getting access to the database and posting every little question on a Internet forum. An UPDATE statement and a REPLACE statement are extremely simple tasks for anyone that even knows basic SQL or programming. If you do not know KEYWORDS and what they do, then study BOL.

Most of the people in this forum are experienced SQL developers that have spent time and effort to learn their skill. You should do the same.

If you have five seconds, take time to look at the members link at the top of the forum. Lookup the member names of the people who have suggested you look at BOL and see how many posts they have. Because these members have been doing this so long, they can judge a person database competency by their posts. I have found the posts here to always be helpful and informative, but this is the second post you have made requesting information on simple tasks.

Just curious robvolk, how can we split the $500 per hour. I sure hope it is not by posts. That only leaves me with what 2 cents?

Jeremy

Go to Top of Page

rrb
SQLTeam Poet Laureate

Australia
1479 Posts

Posted - 03/12/2002 :  20:05:02  Show Profile
OK guys - I think he's got the point.

As for courses - having been a trainer for years - can't say I'd necessarily recommend that you'd go to one and come back knowing how to do a job. It's possible I guess - if you have a simple job.

(Pay out on me if you like, but I've probably been to more courses, and seen more trainers than most - not DB related though - hence my dumb questions.)

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.22 seconds. Powered By: Snitz Forums 2000