September 18, 2007 at 7:49 am
In IS, what is the easiest way to modify a value in a table. I have a table that contains computer names with the domain attached to them.
Example: COMPUTER_NAME.domain.com
I want to drop the .domain.com part and only have COMPUTER_NAME. What is the easiest way to go through that column and remove all of the domains from the computer name?
September 18, 2007 at 8:04 am
Off the top of my head, I would suggest a Derived Column operation with a FINDSTRING call to get the position of the period, then a SUBSTRING call to return only the computer name portion of the string.
September 18, 2007 at 9:40 am
You can use the SQL REPLACE Function in the Select statment if the domain is always the same. Or I agree you will have to use something like SQL CHARINDEX to find the position of the period then use SUBSTRING.
September 18, 2007 at 11:01 am
Used the replace with an update statement, thank you very much.
September 19, 2007 at 12:25 am
Just for grins, next time try charindex to find the first occurence of a period in the field - update field set field = left(field,charindex('.',field)-1)...
Joe
September 19, 2007 at 1:42 am
I am curious though why you ask this question in a SSIS group. Are you loading the values from a source then into a table? If so, why do a SQL update? That is the whole thing about SSIS and the pipeline - transform on the fly. The methods mentioned with a derived column would be the way to go.
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply