split a column and update a new column with part of the split data...

  • 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

  • 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?

  • 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...

  • 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).


  • 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

  • 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