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?very possibleupdate 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|||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|||Problem is... I still do not have the .com part of the username@.domain.com in COL2well, that's easy, just concatenate '.com' onto the end! ;)|||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 ?|||What do you have beside DOMAIN\username in the column?

select COl1, COL2
from daTable
where COL1 not like 'DOMAIN%'|||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|||Try this:

UPDATE myTable
SET COL2 = NULL
WHERE COL2 NOT LIKE '%@.theDomainRecordsToKeep.com'|||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..

Complete Idiot calling all intelligent lifeforms

Hi all, I run several contact websites. One of them is www.ramc.org.uk - they used to fill in a form and results emailed to me. I then manually copied and pasted into page on server. Very tiresome.

I was hoping with sql express i could create a page where they enter there details into the database after verifying details via a link..... Then as the new details are added they would be displayed on the contacts page in alphabetical order.

Is this really possible or even feasable.

Cheers, your all stars. Mark

Yep, almost all websites where you enter data have the data entered into a database of some sort. The data is usually entered through the webpage.|||

Yes it's possible but it will take some code. My experience is in useing Visual Studio, it can be written in Visual Basic.Net, C# or any other language that will run in Visual Studio. On the web page you would create the form and bind the fields to a SQL database. When the user clicks the submit button on your page the data would be stored in your database. Look for someone with some Visual Studio and SQL experience. It's shouldn't to to hard.

Visual Studio and SQL are all Microsoft stuff. Some people do the same thing using Linux, PHP and MySql.

|||Thanks for your help guys. I need to start looking for a 12 yr old who can do it for me. I'm limited to html. I was hoping i could just link the web page to database then results page display the data. Cheers.|||

Simple usage is not overly complex.

I suggest you visit www.ASP.NET. Under the large GREEN #3 [Learn ASP.NET], there are options for:

ASP.NET Video Tutorials
Starter Kits (Ready Made Sites)