Sql query for creating a new column

Question

1.00/5 (2 votes)

See more:

I have data in one column called first name in this format " ANNETTE W * LOW "

i want to make another column called Last name and put all the data in last name after *

input is first name
ANNETTE W * LOW

out put required :

first name last name
ANNETTE W LOW

request a sql query please

What I have tried:

.........................................

Posted 30-Aug-18 7:17am

harshitawkk

Updated 30-Aug-18 20:22pm

Add a Solution

Comments

ZurdoDev 30-Aug-18 13:23pm

Where are you stuck?

harshitawkk 30-Aug-18 13:50pm

i need to your help to get query

ZurdoDev 30-Aug-18 14:01pm

You already said that. Show the code you have written and explain where you are stuck. I won't do all the work for you.

RedDk 30-Aug-18 14:13pm

Use the BOL. It is a very pleasing tome.

MadMyche 30-Aug-18 15:47pm

And what flavor of SQL are you using; MySql, Oracle, (MS) Sql Server?

Santosh kumar Pithani 31-Aug-18 0:03am

Try to learn and put what you have tried to achieve.we always help you;without you effort you don't know about code its vain to put as a solution!

4 solutions

Solution 2

Quote:
do you know how to write query ?

this process i'm aware off

It doesn't quite work like that.
We do not do your work for you.
If you want someone to write your code, you have to pay - I suggest you go to Freelancer.com and ask there.

But be aware: you get what you pay for. Pay peanuts, get monkeys.

The idea of "development" is as the word suggests: "The systematic use of scientific and technical knowledge to meet specific objectives or requirements." BusinessDictionary.com[^]
That's not the same thing as "have a quick google and give up if I can't find exactly the right code".
So either pay someone to do it, or learn how to write it yourself. We aren't here to do it for you.

Posted 30-Aug-18 8:01am

OriginalGriff

Comments

Mohibur Rashid 30-Aug-18 20:33pm

Pay banana, you will get monkey with or without peanut allergy

Solution 3

First thing you are going to need to do is to ALTER the table design to add in the new column
Add Columns to a Table (Database Engine) | Microsoft Docs[^]

The second thing are going to need to know how to do is to UPDATE the data within the table.
UPDATE (Transact-SQL) | Microsoft Docs[^]

The above functions work on every version of MS Sql Server since 2000.
The hard part is going to be creating the information you need for updating what is already in your table.
If you have versions 2016 or 2017, there is a STRING_SPLIT command that you can use.
STRING_SPLIT (Transact-SQL) | Microsoft Docs[^]

If you have an earlier version, you will need to find the scripting to create a similar function.

Posted 30-Aug-18 9:56am

MadMyche

Updated 30-Aug-18 11:33am

v2

Comments

Richard Deeming 30-Aug-18 16:03pm

I think you've put the wrong link under the second paragraph - STRING_SPLIT doesn't work in "every version of MS Sql Server since 2000". :)

MadMyche 30-Aug-18 17:31pm

Darn it, C/P'd the wrong link, will fix, thank you

Solution 4

DECLARE @Table TABLE(Name varchar(100))
insert into @Table values('ANNETTE W * LOW')
select Name,substring(Name,0,charindex('*',Name))FirstName ,SUBSTRING(Name,charindex('*',Name)+1,LEN(Name)-charindex('*',Name))LastName from @Table

Posted 30-Aug-18 20:23pm

kirthiga S

Comments

CHill60 31-Aug-18 4:13am

I was going to suggest this for earlier versions of SQL too, rather than the overhead of introducing a UDF to split the string (useful as that might be for other stuff). I don't think this deserved the downvote so I'm countering it.

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)

Duncan Edwards Jones · Accepted Answer · 2018-08-30T07:39:00

Solution 1

First you need to add the column using the ALTER TABLE command..

Then update the data to insert the surname part

Then update the data to remove the surname from the first name part

Posted 30-Aug-18 7:39am

Duncan Edwards Jones

Comments

harshitawkk 30-Aug-18 13:50pm

do you know how to write query ?

this process i'm aware off

ZurdoDev 30-Aug-18 14:02pm

Just a guess, but I think they don't need it stored that way, just queried that way. I know they said to add a column but I don't think the OP actually is very technical.

Duncan Edwards Jones 30-Aug-18 14:41pm

Well yes - that makes a difference.

Do you want to have a new column in your table, or just to split the query return into two parts?