From one column need to make two different columns

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

please identify which platform you’re on

functions can differ from one to the next

I use oracle sql developer

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

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

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?

Thanks for your reply .

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?

no this query is not working in sql

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

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

What error message(s) did you get?

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

2 Likes

did you include a FROM clause?

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

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

4 Likes

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.