Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Help with updating records in a column. Expand / Collapse
Author
Message
Posted Tuesday, December 17, 2013 12:33 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 19, 2014 2:48 PM
Points: 2, Visits: 32
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.

Post #1523851
Posted Tuesday, December 17, 2013 12:37 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:52 AM
Points: 42,445, Visits: 35,501
What was the code that you tried?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1523852
Posted Tuesday, December 17, 2013 12:39 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 19, 2014 2:48 PM
Points: 2, Visits: 32
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 "."
Post #1523854
Posted Tuesday, December 17, 2013 1:06 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:49 PM
Points: 13,069, Visits: 11,908
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1523868
Posted Tuesday, December 17, 2013 1:49 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:14 AM
Points: 36,735, Visits: 31,186
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1523876
Posted Tuesday, December 17, 2013 2:24 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:25 PM
Points: 3,333, Visits: 7,193
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 '%.%'




Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1523888
Posted Tuesday, December 17, 2013 10:12 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:14 AM
Points: 36,735, Visits: 31,186
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1523941
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse