Creating a new column and inserting data on it from an existing column

• SQLTestUser

  Hall of Fame

  Points: 3997

  More actions

  September 23, 2013 at 10:51 pm

  #298139

  Is there any way we can create a new column based on the results of a select. i am trying to create a new column that would store users address that is currently stored in his name as

  name (nvarchar 500)

  "this,guy (Chicago)"

  into three new columns as

  firstname lastname address

  guy this newyork

  thanks,

• Dwain Camps

  SSC Guru

  Points: 86908

  More actions

  September 23, 2013 at 11:22 pm

  #1652562

  Something like this perhaps?

  WITH SampleData (MyString) AS

  (

  SELECT 'this,guy (Chicago)'

  )

  SELECT MyString

  ,C1=RTRIM(MAX(CASE WHEN ItemNumber = 1 THEN Item END))

  ,C2=RTRIM(MAX(CASE WHEN ItemNumber = 3 THEN Item END))

  ,C3=RTRIM(MAX(CASE WHEN ItemNumber = 5 THEN Item END))

  FROM SampleData a

  CROSS APPLY dbo.PatternSplitCM(MyString, '[a-zA-Z]') b

  WHERE [Matched]=1

  GROUP BY MyString;

  PatternSplitCM can be found in the 4th article in my signature links.

  You may have to mess with it a bit if your actual data is not as clean as your sample.

  ---

  My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

  My thought question: Have you ever been told that your query runs too fast?

  My advice:
  INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
  The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

  Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
  Since random numbers are too important to be left to chance, let's generate some![/url]
  Learn to understand recursive CTEs by example.[/url]
  [url url=http://www.sqlservercentral.com/articles/St

• super48

  SSCrazy

  Points: 2682

  More actions

  September 24, 2013 at 5:20 am

  #1652657

  CREATE TABLE addrname(addnam varchar(50))

  insert into addrname

  values('roshan,hrithik(india)')

  go

  create table separated(firstname varchar(50),lastname varchar(50),address varchar(50))

  go

  insert into separated

  select

  SUBSTRING(addnam,PATINDEX('%,%',addnam)+1,PATINDEX('(%',addnam)-1-PATINDEX('%,%',addnam))as firstname,

  SUBSTRING(addnam,0,PATINDEX('%,%',addnam))as lastname,

  SUBSTRING(addnam,PATINDEX('%(%',addnam)+1,PATINDEX('%)%',addnam)-1-PATINDEX('%(%',addnam))as address

  from addrname

  it looks messy check if it works for you