August 31, 2011 at 5:01 pm
I have a table with the following fields:
RecordNum as int
strDomainName as varchar(255)
strShortName as varchar(255)
So record number is unique. inside of strDomainName the field is something like domainname\username.
I need to loop through all the records one at a time and split the strDomainName field and then take the right side of the result (i.e. username) and update the same row and put the username value in strShortName. The only stipulation is that the strDomainName field does not always have that format so I would like to skip any ones that do not contain the backslash. I was looking at SSIS to do this but I was wondering if this could be done with T-SQL instead.
Thanks for any advice...
Chris
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 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy