December 17, 2013 at 12:33 pm
Hello,
I am completely new to T-SQL and I am learning how to do some complex stuff.
I have a table called TA with columns ServerName, TextA
Table Name : TA
columns 1: Server name
column 2: TextA
in the column "ServerName" I have listed server names like
Serv1 Prod
Serv2 prod
Server3.disc.dim.com prod
server4.dim.com Prod
server5.st.dim.com prod
I need to update the column Servername such a way that I need to remove anything with ".disc.dim.com",".dim.com","st.dim.com"
I tried using replace function but that doesn't seem to help.
Any guidance is much appreciated.
Regards,
Anush.
December 17, 2013 at 12:37 pm
What was the code that you tried?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 17, 2013 at 12:39 pm
Thank you for taking a look, This is the code I have been trying.
UPDATE TA
SET ServerName= REPLACE(ServerName, '%.%.dim.com', '')
WHERE Servername like '%.%.dim.com%'
basically I want to remove anything that starts with first "."
December 17, 2013 at 1:06 pm
anushakadiyala 52521 (12/17/2013)
Thank you for taking a look, This is the code I have been trying.UPDATE TA
SET ServerName= REPLACE(ServerName, '%.%.dim.com', '')
WHERE Servername like '%.%.dim.com%'
basically I want to remove anything that starts with first "."
I think what you are trying to do is to change the ServerName to be dim.com? They way you have this coded it won't actually do anything.
Is this closer to what you want?
UPDATE TA
SET ServerName= 'dim.com'
WHERE Servername like '%.%.dim.com%'
This still probably isn't quite what you are looking for but it is not at all clear what you are trying to do.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 17, 2013 at 1:49 pm
anushakadiyala 52521 (12/17/2013)
Thank you for taking a look, This is the code I have been trying.UPDATE TA
SET ServerName= REPLACE(ServerName, '%.%.dim.com', '')
WHERE Servername like '%.%.dim.com%'
You cannot use wildcards in a REPLACE.
basically I want to remove anything that starts with first "."
The following should do as you ask... I don't know how you operate but I recommend not just blindly using this. Lookup the functions in Books Online (press the {f1} key in SSMS to get there) and teach yourself how they work and why. 😉
UPDATE dbo.TA
SET ServerName = SUBSTRING(ServerName,1,CHARINDEX('.',ServerName)-1)
WHERE ServerName LIKE '%.%'
;
--Jeff Moden
Change is inevitable... Change for the better is not.
December 17, 2013 at 2:24 pm
As far as I understand, it should be more like this to keep the "prod" at the end of the name.
UPDATE TA
SET ServerName = STUFF(ServerName, CHARINDEX('.', Servername), CHARINDEX(' ', Servername) - CHARINDEX('.', Servername), '')
WHERE ServerName LIKE '%.%'
December 17, 2013 at 10:12 pm
Luis Cazares (12/17/2013)
As far as I understand, it should be more like this to keep the "prod" at the end of the name.
UPDATE TA
SET ServerName = STUFF(ServerName, CHARINDEX('.', Servername), CHARINDEX(' ', Servername) - CHARINDEX('.', Servername), '')
WHERE ServerName LIKE '%.%'
Hmmm... I wasn't even looking at the code examples. I was going only by...
basically I want to remove anything that starts with first "."
The code examples do tell a different story, though. Thanks for the catch.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply