August 31, 2011 at 6:07 pm
How about this?
DECLARE @Table Table
(
RecordNum INT IDENTITY(1,1)
,strDomainName VARCHAR(255)
,strShortName VARCHAR(255)
)
INSERT INTO @Table ( strDomainName)
SELECT 'Domain1\User1'
UNION ALL SELECT 'Domain1\User2'
UNION ALL SELECT 'User2' -- See here just UserName, no domain name, what udpate strDomainName with?
UNION ALL SELECT 'Domain2\User1'
UNION ALL SELECT 'Domain2' -- See here just DomainName, no USer name, what udpate strShortName with?
SELECT * FROM @Table
UPDATE @Table
--SELECT RecordNum,
SET strDomainName =
CASE WHEN CHARINDEX('\' , strDomainName) > 0
THEN LEFT(strDomainName , CHARINDEX('\' , strDomainName) -1)
ELSE strDomainName
END ,
strShortName =
CASE WHEN CHARINDEX('\' , strDomainName) > 0
THEN STUFF ( strDomainName , 1, CHARINDEX('\' , strDomainName), '')
ELSE NULL
END
FROM @Table
SELECT * FROM @Table
Look closely at the sample data; there are 2 special cases. What are you going to do if you face them?
September 1, 2011 at 1:39 am
I'm not sure what you are asking but I'm kind of understanding the code. I need to ignore anything in the strDomainName field that does not have a backslash. If it has one, I just need to split the field and then update the strshortname. The only thing that does not have a backslash in the strDomainName field will have an IP address...
September 1, 2011 at 2:05 am
The code sample provided by ColdCoffee works fine except that it also assumed you would want to update strDomainName. So just exclude that part of the update and you're done:
UPDATE Table
SET strShortName = STUFF ( strDomainName , 1, CHARINDEX('\' , strDomainName), '')
FROM Table
where CHARINDEX('\' , strDomainName) > 0
Also, a few words of advice about SSIS: it's generally not the best tool if all of the processing is happening inside a single SQL Server instance (where T-SQL probably does a perfectly good job, without the overhead of starting up a package).
September 1, 2011 at 6:57 am
stupid48 (8/31/2011)
I need to loop through all the records one at a time
You're not thinking about this as a set-based operation. As Jeff Moden recommends in his signature, the best way to start thinking in terms of sets is to think about what you want to do to a column instead of a row.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 1, 2011 at 8:36 am
Thanks a lot for all the info. I definitely have a great deal to learn....
That worked like a charm....
Viewing 5 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply