| Author |
Topic |
|
INFLICT
Starting Member
15 Posts |
 Posted - 2002-03-08 : 11:38:25
|
| 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
468 Posts |
Posted - 2002-03-08 : 11:44:24
|
Take a look at 'replace' in BOLquote:
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 |
 |
|
|
INFLICT
Starting Member
15 Posts |
Posted - 2002-03-08 : 11:53:15
|
| 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. |
|
|
|
INFLICT
Starting Member
15 Posts |
Posted - 2002-03-08 : 12:05:06
|
| 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!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-03-08 : 12:08:07
|
| 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 |
|
|
|
Jay99
468 Posts |
Posted - 2002-03-08 : 12:11:44
|
quote:
...give me an exact statement? My boss is waiting for this to be done. Since yesterday, please I need help quick!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
 Jay |
|
|
|
INFLICT
Starting Member
15 Posts |
Posted - 2002-03-08 : 12:14:44
|
| 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. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-03-08 : 12:15:17
|
| 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 |
|
|
|
INFLICT
Starting Member
15 Posts |
Posted - 2002-03-08 : 12:33:17
|
| 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. |
|
|
|
Jay99
468 Posts |
Posted - 2002-03-08 : 13:12:01
|
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 |
|
|
|
JustinBigelow
SQL Gigolo
1157 Posts |
Posted - 2002-03-08 : 13:13:18
|
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 |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2002-03-11 : 19:50:03
|
| JayGreat post. I think that might need to become an article.Damian |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-03-11 : 21:35:42
|
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" |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-03-11 : 21:37:29
|
| PS - agree with Damian.PPS - well guess where [url]http://www.inflict.com[/url] leads!Edited by - rrb on 03/11/2002 21:40:10Edited by - rrb on 03/11/2002 21:41:11 |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2002-03-12 : 18:21:13
|
quote:
When I was a musician, people used to tell me "don't give up your day-job"
Bloody musicians....they are everywhere!Damian |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-03-12 : 18:25:54
|
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" |
|
|
|
joldham
Wiseass Yak Posting Master
300 Posts |
Posted - 2002-03-12 : 19:34:25
|
| 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 |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-03-12 : 20:05:02
|
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" |
|
|
|
|
Updating e-mail domains