如何为这种情况创建查询 [英] How can create a query for this situation

问题描述

我有一张桌子跟随：

员工（ID，FULL_NAME，GENDER，PROVINCE，AGE）

和我设定值如下：

  INSERT   INTO 员工（ID，FULL_NAME，GENDER，PROVINCE，AGE）
  VALUES （ 1 ，'  David Jam'，'  male'，'  LA' ， 20 ），
（ 2 ，'  ELENA QUEEN'，'  female'，'  LA'， 21 ）

你可以看到我的 FU LL_NAME 字段有空格。我们可以知道它分为名字和姓氏。现在我想用一个查询来选择员工的第一个名字。

谁知道这个问题？



谢谢

解决方案

- 试试........

 选择  LEFT （FULL_NAME，CHARINDEX（' '，FULL_NAME， 0 ））来自 EMPLOYEE 

我认为OriginalGriff认为，将名字和姓氏连接到同一个字段是一个坏主意。正如已经指出的那样将它们存储在单独的列中，您的操作将更加简单。



要抛出另一种解决方案，通常最后一个空格后的字符串是最后一个为了找到你所知道的姓氏，请点击名称

  SELECT  REVERSE（SUBSTRING（REVERSE） （FULL_NAME）， 0 ，CHARINDEX（' '，REVERSE（FULL_NAME））））
  FROM  EMPLOYEE; 

但如上所述，最好的选择是单独存储它们。

你可以做到 - 但这不是一个好主意。一个更好的解决方案是将它拆分为在单独的字段中保存名字和第二名（如果只是因为有些人拥有并使用三个名称，所以假设单个空格将它们分开不一定是真的。 />
但这将拆分它们并返回第一个字：

  SELECT  SUBSTRING（FULL_NAME， 0 ，CHARINDEX（' '，FULL_NAME）） AS  [名字]  FROM  EMPLOYEE 

I have a table follow:

EMPLOYEE(ID, FULL_NAME, GENDER, PROVINCE, AGE)

and I set value follow:

INSERT INTO EMPLOYEE(ID, FULL_NAME, GENDER, PROVINCE, AGE)
VALUES(1, 'David Jam', 'male', 'LA', 20),
(2, 'ELENA QUEEN', 'female', 'LA', 21)

You can see my FULL_NAME field has a space. And we can know it divides to first name and last name. Now I want to use a query to SELECT the first name of employee.
Who have any idea for this problem?

Thank you

解决方案

--Try it........

select LEFT(FULL_NAME, CHARINDEX(' ',FULL_NAME,0)) from EMPLOYEE

I seconf OriginalGriff's opinion, concatenating first and last name to the same field is a bad idea. As already pointed out store them in separate columns and your operations will be much simpler.

To throw another kind of solution, typically the string after the last space is the last name so in order to find the last name you cuold have something like

SELECT REVERSE( SUBSTRING( REVERSE(FULL_NAME), 0, CHARINDEX (' ', REVERSE(FULL_NAME))))
FROM EMPLOYEE;

But as said, the best option is to store them separately.

You can do it - but it's not a good idea. A better solution is to split it to hold the "first name" and "second name" in separate fields (if only because some people have and use three names, so an assumption that a single space separates them is not necessarily true.
But this will split them and return the first "word":

SELECT SUBSTRING(FULL_NAME, 0, CHARINDEX (' ', FULL_NAME)) AS [First Name] FROM EMPLOYEE

这篇关于如何为这种情况创建查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！