sql从一个字段创建一个字段 [英] sql create a field from a field

问题描述

我需要运行一个查询，该查询将从具有2种数据类型的字段中提取信息.

I need to run a query that would pull information from a field that has 2 types of data .

字段是地址，数据为123，地址为[e-mail].

Field is address and has 123 avenue as data and [e-mail].

我需要在表客户和字段地址中为电子邮件添加两个字段，并为一个STaddress字段创建一个字段?

I need to make 2 fields one for email and one STaddress from table customer and field address?

我可以协助任何人.. 它的访问权限和vb查询

anyone can i assis.. its access and vb query

我想到了

从客户的" @ "之类的地址中选择"customer.address".

Select customer.address from customer where address like "@"

但是我仍然需要将地址字段的数据显示为2个不同的字段...

but still i need to display the the data of address field to 2 different fields...

推荐答案

基于此问题和重复的问题，我了解您的表格中的字段包含街道地址和电子邮件地址，以及您想将它们分成单独的字段.

Based on this question and your duplicate question, I understand your table has a field which includes both the street address and email address and you want to split those into separate fields.

所以您的桌子上包括这个...

So your table includes this ...

YourField
------------------------------
1234 ave willie [e-mail]
123 avenue [e-mail]

你想要这个...

YourField                       street_address  email_address
------------------------------  --------------- --------------
1234 ave willie [e-mail]  1234 ave willie [e-mail]
123 avenue [e-mail]         123 avenue      [e-mail]

如果正确，则可以使用InstrRev()函数确定YourField中最后一个空格的位置.最后一个空格之前的所有内容均为街道地址；后面的所有内容都是电子邮件地址.

If that is correct, you can use the InstrRev() function to determine the position of the last space in YourField. Everything before the last space is the street address; everything after is the email address.

SELECT
    y.YourField,
    Left(y.YourField, InstrRev(y.YourField, ' ') -1) AS street_address,
    Mid(y.YourField, InstrRev(y.YourField, ' ') +1) AS email_address
FROM YourTable AS y;

您可能需要添加WHERE子句以确保查询仅尝试评估包含期望的YourField值模式的行.

You may need to add a WHERE clause to ensure the query only tries to evaluate rows which include your expected YourField value patterns.

这篇关于sql从一个字段创建一个字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！