none
Extract Data from a Column and insert into a table

    Question

  • Hi All ,

    I have a column that has a different pattern of data like below . There are around 400 records in that table with 3 columns

    id,centercode,name

    1,xyz-xxx-ancientpaints, ancientpaints

    2.xyz-newgen,newgen

    3xyz-ryz-tracknew,tracknew

    i want to extract the data which is in between the 2 dashes from the centercode column

    like here it would be xxx and ryz .  After the data is extracted i want  to create a table of this extracted records in a different  db . SO the new table will contain data like

    id,centercode,name

    1,xxx,ancientpaints

    3,ryz,tracknew

    Kindly help

    Saturday, October 19, 2013 6:03 AM

Answers

  • Hi All ,

    I have a column that has a different pattern of data like below . There are around 400 records in that table with 3 columns

    id,centercode,name

    1,xyz-xxx-ancientpaints, ancientpaints

    2.xyz-newgen,newgen

    3xyz-ryz-tracknew,tracknew

    i want to extract the data which is in between the 2 dashes from the centercode column

    like here it would be xxx and ryz .  After the data is extracted i want  to create a table of this extracted records in a different  db . SO the new table will contain data like

    id,centercode,name

    1,xxx,ancientpaints

    3,ryz,tracknew

    Kindly help

    Hi,

    Try like this,

    DECLARE @tmp TABLE (id INT,centercode VARCHAR(50),name VARCHAR(50))
    INSERT @tmp SELECT  1,'xyz-xxx-ancientpaints', 'ancientpaints'
    INSERT @tmp SELECT  2,'xyz-newgen','newgen'
    INSERT @tmp SELECT  3,'xyz-ryz-tracknew','tracknew'
    SELECT * FROM (
    SELECT id,
    CASE WHEN LEN(centercode)-LEN(REPLACE(centercode,'-','')) = 1 THEN NULL
    ELSE PARSENAME(REPLACE(centercode,'-','.'),2) END centercode,
    name 
    FROM @tmp ) tmp
    WHERE centercode IS NOT NULL


    sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.

    Saturday, October 19, 2013 6:23 AM
  • Hi ,

    Yes but lastly i forgot to mention that db is on a different server :( . It solves the problem if you have the other DB on the same server .

    Hi,

    Then you should try with four part naming convention ,

    Servername.databasename.schemaname.tablename

    Also try this link - http://stackoverflow.com/questions/189422/how-do-i-create-and-query-linked-database-servers-in-sql-server/189431#189431

    OPENROWSET

    OPENQUERY

    Linked Server - create linked server if it is not one time job .


    sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.




    Saturday, October 19, 2013 7:02 AM

All replies

  • Hi All ,

    I have a column that has a different pattern of data like below . There are around 400 records in that table with 3 columns

    id,centercode,name

    1,xyz-xxx-ancientpaints, ancientpaints

    2.xyz-newgen,newgen

    3xyz-ryz-tracknew,tracknew

    i want to extract the data which is in between the 2 dashes from the centercode column

    like here it would be xxx and ryz .  After the data is extracted i want  to create a table of this extracted records in a different  db . SO the new table will contain data like

    id,centercode,name

    1,xxx,ancientpaints

    3,ryz,tracknew

    Kindly help

    Hi,

    Try like this,

    DECLARE @tmp TABLE (id INT,centercode VARCHAR(50),name VARCHAR(50))
    INSERT @tmp SELECT  1,'xyz-xxx-ancientpaints', 'ancientpaints'
    INSERT @tmp SELECT  2,'xyz-newgen','newgen'
    INSERT @tmp SELECT  3,'xyz-ryz-tracknew','tracknew'
    SELECT * FROM (
    SELECT id,
    CASE WHEN LEN(centercode)-LEN(REPLACE(centercode,'-','')) = 1 THEN NULL
    ELSE PARSENAME(REPLACE(centercode,'-','.'),2) END centercode,
    name 
    FROM @tmp ) tmp
    WHERE centercode IS NOT NULL


    sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.

    Saturday, October 19, 2013 6:23 AM
  • One solution is:

    DECLARE @table TABLE (id int,centercode varchar(100),name varchar(100))
    
    
    INSERT @table
            ( id, centercode, name )
    VALUES  ( 1, 'xyz-xxx-ancientpaints', 'ancientpaints' ) ,
    ( 2, 'xyz-newgen', 'newgen' ) ,
    ( 3, 'xyz-ryz-tracknew', 'tracknew' ) 
    
    
    SELECT 
    	id, 
    	LEFT(RIGHT(centercode, LEN(centercode) - CHARINDEX('-',centercode)), CHARINDEX('-', RIGHT(centercode, LEN(centercode) - CHARINDEX('-',centercode))) - 1) AS centercode,
    	RIGHT(RIGHT(centercode, LEN(centercode) - CHARINDEX('-',centercode)), LEN(RIGHT(centercode, LEN(centercode) - CHARINDEX('-',centercode))) - CHARINDEX('-', RIGHT(centercode, LEN(centercode) - CHARINDEX('-',centercode))) ) AS name
    FROM @table
    WHERE CHARINDEX('-', RIGHT(centercode, LEN(centercode) - CHARINDEX('-',centercode))) > 0


    Saeid Hasani, sqldevelop.wordpress.com

    Download Books Online for SQL Server 2012

    Saturday, October 19, 2013 6:27 AM
  • Hi ,

    Thanks a lot for the reply but now if i wish to create a new table with these records  in a new Db . How will that workout .Above code solves the problem of the extraction of the required data from the field.

    Thanks Priya

    Saturday, October 19, 2013 6:28 AM
  • Hi ,

    Thanks a lot for the reply but now if i wish to create a new table with these records  in a new Db . How will that workout .Above code solves the problem of the extraction of the required data from the field.

    Thanks Priya

    Hi Priya,

    Just try with INTO clause , like shown below

    DECLARE @tmp TABLE (id INT,centercode VARCHAR(50),name VARCHAR(50))
    INSERT @tmp SELECT  1,'xyz-xxx-ancientpaints', 'ancientpaints'
    INSERT @tmp SELECT  2,'xyz-newgen','newgen'
    INSERT @tmp SELECT  3,'xyz-ryz-tracknew','tracknew'
    SELECT * INTO databasename.dbo.newtable FROM (
    SELECT id,
    CASE WHEN LEN(centercode)-LEN(REPLACE(centercode,'-','')) = 1 THEN NULL
    ELSE PARSENAME(REPLACE(centercode,'-','.'),2) END centercode,
    name 
    FROM @tmp ) tmp 
    WHERE centercode IS NOT NULL


    sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.

    Saturday, October 19, 2013 6:33 AM
  • You can create a new table, then insert into it. Or use select..into..from pattern like this:

    SELECT 
    	id, 
    	LEFT(RIGHT(centercode, LEN(centercode) - CHARINDEX('-',centercode)), CHARINDEX('-', RIGHT(centercode, LEN(centercode) - CHARINDEX('-',centercode))) - 1) AS centercode,
    	RIGHT(RIGHT(centercode, LEN(centercode) - CHARINDEX('-',centercode)), LEN(RIGHT(centercode, LEN(centercode) - CHARINDEX('-',centercode))) - CHARINDEX('-', RIGHT(centercode, LEN(centercode) - CHARINDEX('-',centercode))) ) AS name
    INTO [New database].dbo.NewTable
    FROM @table
    WHERE CHARINDEX('-', RIGHT(centercode, LEN(centercode) - CHARINDEX('-',centercode))) > 0


    Saeid Hasani, sqldevelop.wordpress.com

    Download Books Online for SQL Server 2012

    Saturday, October 19, 2013 6:33 AM
  • Sorry Sathya, I didn't see your post. Just in the same time!!!

    Saeid Hasani, sqldevelop.wordpress.com

    Download Books Online for SQL Server 2012

    Saturday, October 19, 2013 6:35 AM
  • Sorry Sathya, I didn't see your post. Just in the same time!!!

    Saeid Hasani, sqldevelop.wordpress.com

    Download Books Online for SQL Server 2012


    Not a problem Saeid , It happens for me too several times :^)

    sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.

    Saturday, October 19, 2013 6:41 AM
  • Hi ,

    Yes but lastly i forgot to mention that db is on a different server :( . It solves the problem if you have the other DB on the same server .

    Saturday, October 19, 2013 6:47 AM
  • Hi ,

    Yes but lastly i forgot to mention that db is on a different server :( . It solves the problem if you have the other DB on the same server .

    Hi,

    Then you should try with four part naming convention ,

    Servername.databasename.schemaname.tablename

    Also try this link - http://stackoverflow.com/questions/189422/how-do-i-create-and-query-linked-database-servers-in-sql-server/189431#189431

    OPENROWSET

    OPENQUERY

    Linked Server - create linked server if it is not one time job .


    sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.




    Saturday, October 19, 2013 7:02 AM