From one column need to make two different columns

[harshitawk]

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

[r937]

please identify which platform you’re on

functions can differ from one to the next

[harshitawk]

I use oracle sql developer

[r937]

SELECT SUBSTRING(column FROM 1 FOR LOCATE(column,'*')-1) AS first_name
     , SUBSTRING(column FROM LOCATE(column,'*')+1) AS last_name

[harshitawk]

Thanks for query .

how this will add a column and implement the change in entire column .

i’m using oracle sql developer. where in my emp table i have data in column called name as

ANNETTE W * LOW now i need to carete new column called last name and split data staring after *

now can you please share the query

[Mittineague]

You have the query for returning “first_name” and “last_name”. Can’t you simply use that for an INSERT / UPDATE into the new table / fields?

[harshitawk]

Thanks for your reply .

which query are you referring too ?

[Mittineague]

@harshitawk

which query are you referring too ?

The one that was handed to you:

SELECT SUBSTRING(column FROM 1 FOR LOCATE(column,'*')-1) AS first_name
     , SUBSTRING(column FROM LOCATE(column,'*')+1) AS last_name

Did you try running that to see if it does what you want it to?

[harshitawk]

no this query is not working in sql

[m_hutley]

… did you replace ‘column’ with your column name?

[harshitawk]

really why will i keep column like this . yes i changed

[Mittineague]

What error message(s) did you get?

[rpkamp]

Well, if we got a dollar for every time somebody copy/pasted a piece of code blindly and then complains it doesn’t work… Let’s just say we wouldn’t be on these forums anymore.

Nothing personal. It’s just it can be a bit frustrating at times is all.

Good on you for not being one of those people

[r937]

@harshitawk

no this query is not working in sql

did you include a FROM clause?

[harshitawk]

Thanks R937, apologies if i mentioned it wrongly i’m using microsoft sql server studio and the query i’m using is

            update test set
      first_name = rtrim(substring(first_name, 1, instr(first_name, '*') - 1)),
  last_name = ltrim(substring (first_name, instr(first_name, '*') + 1));                         

However, it giving me error saying that substring can only 3 arguments.please help

[r937]

@harshitawk

apologies if i mentioned it wrongly i’m using microsoft sql server studio

that is the biggest, hugest, most astonishing typo i’ve ever seen – somehow trying to type “microsoft sql server studio” and all of a sudden “oracle sql developer” accidentally pops out

so i showed you how to do it with oracle syntax

now all you have to do is come up with the exact same idea using sql server functions

hint: look up SUBSTRING in the sql server manual for instructions on the 3 parameters you need