Find website from email address

Hi,

I have 2 fields in a database: 'email' and 'website'.

In the majority of cases, the website field is not populated even though the
email address is. In 90% of cases, the website will be 'www.' followed by
whatever is after the '@.' symbol in the email address.

Would anyone be able to help me with the SQL that would take whatever is
after the '@.' sign in the email address, add it to 'www.' and populate the
'website' field?

Thanks!Would anyone be able to help me with the SQL that would take whatever

Quote:

Originally Posted by

is after the '@.' sign in the email address, add it to 'www.' and
populate the 'website' field?

UPDATE yourtable
SET Site = SUBSTRING(yourtable.email, CHARINDEX('@.', yourtable.email) + 1,
LEN(yourtable.email) - CHARINDEX('@.', yourtable.email))

--
PBsoft di Gabriele Bertolucci
www.pbsoft.it
skype:pbsoftsolution|||Would anyone be able to help me with the SQL that would take whatever is

Quote:

Originally Posted by

after the '@.' sign in the email address, add it to 'www.' and populate the
'website' field?

Here's one method:

UPDATE dbo.MyTable
SET website =
CASE WHEN CHARINDEX('@.', email) 0 AND CHARINDEX('@.', email) + 1 <
LEN(email) THEN
'www.' + SUBSTRING(email, CHARINDEX('@.', email) + 1, 255)
ELSE
website
END
WHERE
website IS NULL OR
website = ''

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Mintyman" <mintyman@.ntlworld.comwrote in message
news:f6gap5$qgb$1$8300dec7@.news.demon.co.uk...

Quote:

Originally Posted by

Hi,
>
I have 2 fields in a database: 'email' and 'website'.
>
In the majority of cases, the website field is not populated even though
the email address is. In 90% of cases, the website will be 'www.' followed
by whatever is after the '@.' symbol in the email address.
>
Would anyone be able to help me with the SQL that would take whatever is
after the '@.' sign in the email address, add it to 'www.' and populate the
'website' field?
>
Thanks!
>

|||Cheers guys! Much appreciated :o)

"Mintyman" <mintyman@.ntlworld.comwrote in message
news:f6gap5$qgb$1$8300dec7@.news.demon.co.uk...

Quote:

Originally Posted by

Hi,
>
I have 2 fields in a database: 'email' and 'website'.
>
In the majority of cases, the website field is not populated even though
the email address is. In 90% of cases, the website will be 'www.' followed
by whatever is after the '@.' symbol in the email address.
>
Would anyone be able to help me with the SQL that would take whatever is
after the '@.' sign in the email address, add it to 'www.' and populate the
'website' field?
>
Thanks!
>

Find a missing number ?

We have a database table, with a field ( int (4) ) , we'll call it
transid.
The field is being populated by an unknown random formula in a third party
program.
I have another program that i am writing that needs to insert records into
this table.
This field, transid must be unique.
I started in MY program with a function that said "select max(transid) from
table" , newid=max(transid) + 1, insert into table (newid,blah blah blah)
Problem being that because the OTHER program is using random numbers, it
eventually put in a value that is the max length of an int(4) field.
SO - i changed my syntax to be newid=(select max(transid) from table where
transid<1000000)+1 - works fine for a while until the other program came
along and inserted a value of 9999996 - so then, only my next four
transactions worked until i hit 9999999 and then the number kept repeating,
no longer unique, insert fails.
SO my question - is there a sql function that would say:
"Find me a number that is not already used in the field transid in table" '
Im trying to figure with EXISTS, NOT EXISTS, etc, but i cant make anything
work.
Appreciate any assistance !
Thanks,
M.Coz wrote:

> SO my question - is there a sql function that would say:
> "Find me a number that is not already used in the field transid in
> table" ' Im trying to figure with EXISTS, NOT EXISTS, etc, but i
> cant make anything work.
See this thread
SQL Problem: How to find UNUSED numbers
http://groups.google.pl/groups?hl=p...&rnu
m=1
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.

Find a missing number ?

We have a database table, with a field ( int (4) ) , we'll call it
transid.
The field is being populated by an unknown random formula in a third party
program.
I have another program that i am writing that needs to insert records into
this table.
This field, transid must be unique.
I started in MY program with a function that said "select max(transid) from
table" , newid=max(transid) + 1, insert into table (newid,blah blah blah)
Problem being that because the OTHER program is using random numbers, it
eventually put in a value that is the max length of an int(4) field.
SO - i changed my syntax to be newid=(select max(transid) from table where
transid<1000000)+1 - works fine for a while until the other program came
along and inserted a value of 9999996 - so then, only my next four
transactions worked until i hit 9999999 and then the number kept repeating,
no longer unique, insert fails.
SO my question - is there a sql function that would say:
"Find me a number that is not already used in the field transid in table" '
Im trying to figure with EXISTS, NOT EXISTS, etc, but i cant make anything
work.
Appreciate any assistance !
Thanks,
M.Coz wrote:
> SO my question - is there a sql function that would say:
> "Find me a number that is not already used in the field transid in
> table" ' Im trying to figure with EXISTS, NOT EXISTS, etc, but i
> cant make anything work.
See this thread
SQL Problem: How to find UNUSED numbers
http://groups.google.pl/groups?hl=pl&lr=&ie=UTF-8&oe=UTF-8&th=a3eb815a529ae1c5&rnum=1
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.