2

I have to run a query in Access sql or using the Query Wizard to split the data of field into 2.

The field has data such as 1234 ave willie and has email addresses such as haha@yahoo.com .

I need to put 1234 ave willie in a new field named St address and haha@yahoo.com in Email.

Can someone help? Is there a wildcard operator like LIKE etc?

Danny Beckett
  • 20,529
  • 24
  • 107
  • 134
kenmru
  • 59
  • 7

1 Answers1

0

You should look into using LIKE to determine which field is an email and which field is an address. Something like this would work:

SELECT Field, 'Email' FieldType
FROM YourTable
WHERE Field Like '*@*'
UNION ALL
SELECT Field, 'Address' FieldType
FROM YourTable
WHERE Field Not Like '*@*'

You mention you need to split these in 2 separate fields -- depends on the table structure. But assuming you add 2 new fields to the original table, then something like this should work:

UPDATE YourTable SET Email = Field WHERE Field Like '*@*'
UPDATE YourTable SET Address = Field WHERE Field Not Like '*@*'

You can find a better algorithm to search by email address -- this is just an example. But assuming any field with an @ symbol is an email, then the above will work fine. Here's an SO post with SQL Email validation that can help get you started:

Sql script to find invalid email addresses

Community
  • 1
  • 1
sgeddes
  • 62,311
  • 6
  • 61
  • 83
  • My understanding of OP's situation that both Street Address and E-mail Address will appear in the **same** field. Thus a need to **split**. – PM 77-1 Mar 03 '13 at 03:18
  • @PM77-1 -- My understanding was the field stored either the address or the email -- I could have been mistaken. Need further explanation from OP to fully understand. – sgeddes Mar 03 '13 at 03:27
  • Exactly that field has either a street adress or an email adress.So i need to display email in a field and street adress in another field – kenmru Mar 03 '13 at 17:49
  • I need to use this in access... – kenmru Mar 03 '13 at 18:07
  • I think i need to use instr() and substr() – kenmru Mar 03 '13 at 19:16
  • non of the above works.. – kenmru Mar 03 '13 at 19:29
  • We are very close... again il explain.. I have a field called contact it has either email adress or street adreeses. When i run my query i need to tell it put all email address in 1 other field called email and all the street address in another field name address. So the contact field has either an email or street addrees? – kenmru Mar 03 '13 at 20:34
  • data are as follows in table: CustomerName Contact Rico 34 mills ave James bb@yahoo.com Dav 567 rick avenue Dav 567 rick avenue I need query like this: Customer Name Email Address Rico 34 mills ave James bb@yahoo.com – kenmru Mar 03 '13 at 20:38
  • CustomerName Contact Rico 34 mills ave James bb@yahoo.com Dav 567 rick avenue I need query like this: Customer Name Email Address Rico 34 mills ave James bb@yahoo.com Dav 567 rick avenue – kenmru Mar 03 '13 at 20:45