How can create a query for this situation

Question

0.00/5 (No votes)

See more:

I have a table follow:

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

and I set value follow:

SQL

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

Posted 16-Jul-15 22:34pm

Bùi Long Nghĩa

Updated 16-Jul-15 23:28pm

Abhishek Pant

v2

Add a Solution

Comments

F-ES Sitecore 17-Jul-15 4:42am

Select the full_name field and separate the name in your code, or better yet store the firstname\lastname values in their own fields and when you need to show the full name you just combine them together in your code.

Bùi Long Nghĩa 17-Jul-15 4:46am

Can you give to me a clear example for separate the name?

F-ES Sitecore 17-Jul-15 4:56am

You haven't said what programming language you are using. Regardless, just google "separate string on words <insert name of your language here>"

Bùi Long Nghĩa 17-Jul-15 5:00am

I am using SQL Server

F-ES Sitecore 17-Jul-15 5:10am

Your SQL code shouldn't be doing things like splitting strings, that should be done by the presentation component. If you need the first name as a data entity then it should be stored in its own field. However I'm sure if you google "split string sql" you'll still find plenty of examples.

Bùi Long Nghĩa 17-Jul-15 5:20am

ok. Thank you so much

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Godhaniketan · Answer 1 · 2015-07-16T23:22:00

Solution 1

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

SQL

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

Posted 16-Jul-15 23:22pm

Godhaniketan

Comments

[no name] 17-Jul-15 6:03am

if it is useful then mark answer as solution. thanks in advance.

Wendelius · Answer 2 · 2015-07-16T23:40:00

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

SQL

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

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

OriginalGriff · Answer 3 · 2015-07-16T23:24:00

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":

SQL

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

How can create a query for this situation

3 solutions

Solution 1

Solution 3

Solution 2

Add your solution here

Preview 0