Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2004
    Posts
    99

    Unanswered: complicated SQL help

    Perhaps you can help with something that I consider kind of omplicated? (I am SO hoping that I am NOT going to have to do this manually!!!)

    I have a table (about 3000 rows) where two of the columns have Domain User information.

    COL1 has DOMAIN\Username and COL2 has (or SHOULD have) DOMAIN@username.com

    I need to look at each field in COL1 and if exists DOMAIN\username, I need to populate COL2 with username@DOMAIN.com

    Is this possible???

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    very possible
    Code:
    update daTable
       set COL2 = substring(COL1
                           ,charindex('\',COL1)+1
                           ,LEN(COL1)-charindex('\',COL1)
                           ) 
                  + '@' +
                  left(COL1,charindex('\',COL1)-1)
     where coalesce(COL1,'') > ''
    tip: back up your data before trying this
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2004
    Posts
    99
    Thank you! That's a start! I learn so much from these forums!!

    Problem is... I still do not have the .com part of the username@domain.com in COL2

    I also found out that once I populate COL2 correctly, I need to CLEAR any field in COL2 that does not adhere to "username@domain.com" and then change domain to correctdomain so that all of the poplated fields in COL2 will read username@correctdomain.com

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by jpotucek
    Problem is... I still do not have the .com part of the username@domain.com in COL2
    well, that's easy, just concatenate '.com' onto the end!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2004
    Posts
    99
    Thanks.. what about the fields that got pulled over that don't adhere to the username@Domain.com criteria? Is there a way to clear any fields that are not LIKE username@Domain.com ?

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    What do you have beside DOMAIN\username in the column?

    Code:
    select COl1, COL2
    from daTable
    where COL1 not like 'DOMAIN%'

  7. #7
    Join Date
    Mar 2004
    Posts
    99
    My COL2 is now correct. all fields have username@DOMAIN.com

    What I need to do is clear or delete any fields that do not have specially
    username@domain.com

    in other words. I need to keep all fields in COL2 that are LIKE @DOMAIN.com
    and delete all others (some are username@anotherdomain.com or username@yetanotherdomain.com)

    I tried
    Delete from MYTABLE
    Where COL2 LIKE '%@anotherdomain.com%'

    but that deleted the whole row.. I need to just clear the field in COL2

  8. #8
    Join Date
    Oct 2004
    Location
    New Jersey
    Posts
    65
    Try this:

    Code:
    UPDATE myTable
    SET COL2 = NULL
    WHERE COL2 NOT LIKE '%@theDomainRecordsToKeep.com'
    Retired Moderator at Xtreme VB Talk, for all of your Visual Basic needs.

  9. #9
    Join Date
    Mar 2004
    Posts
    99
    THANK YOU!!!

    This is the code I ended up using..

    UPDATE MYTABLE
    SET COL2= PARSENAME(REPLACE(COL1, '\', '.'), 1) + '@' +
    PARSENAME(REPLACE(COL1, '\', '.'), 2) + '.com'
    WHERE COL1 LIKE '%DOMAIN\%'
    go
    UPDATE MYTABLE
    SET COL2 = Replace(COL2,'DOMAIN','CORRECTDOMAIN')
    go

    Seems to work..

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •