Remove the double Quotes RRS feed

  • Question

  • Hi Experts,

    I have data loaded into sql server DB which is containing data in the below format into a single column

    "         ","3800","827","         ","         ","       ","51345","R","S","          10.00000"    

    Could any please assist with converting it into separate columns

    So it will be mapped into another table columns and will be loaded like below 

    SalaryCode  SalaryID   Department  Region       Area      TypeCode     ShortCode     ManagerCode    Units

                         3800        827                                            51345              R                       S                    10.00000

    Thanks & regards


    Thursday, February 25, 2016 4:31 AM


All replies

  • Hi Priya,

    Use below code

        PARSENAME(REPLACE(Colname,',','.'),1) 'SalaryCode'
    PARSENAME(REPLACE(Colname,',','.'),2) 'SalaryID' ,

     PARSENAME(REPLACE(Colname,',','.'),9) 'Units' ,

    Please mark as answer if my post is helped to solve your problem

    and vote as helpful if it helped so that forum users can benefit 


    Thursday, February 25, 2016 6:04 AM
  • You need to use a string parsing UDF for that


    FROM Table t
    CROSS APPLY dbo.ParseValues(t.[Column],'","')f

    UDF will look like this

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, February 25, 2016 6:10 AM
  • If the data is from a flat file, next time when importing, using a flat file source setting Text qualifier " in SSIS,  then data would get splitted and clean after imported, you won't face such situation. Check here.

    Eric Zhang
    TechNet Community Support

    Thursday, February 25, 2016 7:05 AM
  • Your data were not imported properly and  you should find a right way to import them. You can fix the existing one column data through exporting them to a text file and use BULK INSERT them to your target table and make sure you have the right column list and matching order. I use a varchar type to load into a sample target table. Here is a sample for the whole process.

    create table testtablewithOneColumn (col0 varchar(4000))
    Insert into testtablewithOneColumn values('"Some data        ","3800","827","         ","         ","       ","51345","R","S","          10.00006" ')
    ,('"         ","3800","827","         ","         ","       ","51345","R","S","          10.00008" ')
    ,('"row3         ","3800","827","         ","         ","       ","51345","R","S","          10.00009" ')
    CREATE TABLE testtablewithTargetColumns (
    SalaryCode VARCHAR(50)
    ,SalaryID VARCHAR(50)
    ,Department VARCHAR(50)
    ,Region VARCHAR(50)
    ,Area VARCHAR(50)
    ,theMisingCol VARCHAR(50)
    ,TypeCode VARCHAR(50)
    ,ShortCode VARCHAR(50)
    ,ManagerCode VARCHAR(50)
    ,Units VARCHAR(50)) 
    DECLARE @cmd1 NVARCHAR(4000) 
    set @cmd1 = 'BCP [mydb1].[dbo].testtablewithOneColumn OUT  "C:\temp\myTableData.txt" -c -r \"\n  -T -S'+ @@servername
    exec master..xp_cmdshell @cmd1
    -- Export to a text file
    BULK INSERT  mydb1.[dbo].testtablewithTargetColumns
      FROM 'C:\temp\myTableData.txt'
            FIELDTERMINATOR ='","',
            ROWTERMINATOR ='" "\n',
    		DATAFILETYPE = 'char'
    ---remove the first  double quote
    Update  mydb1.[dbo].testtablewithTargetColumns
    SalaryCode=Replace(SalaryCode,'"', '')  
      --Check the target table
    select SalaryCode
    	 , SalaryID, Department, Region, Area, theMisingCol, TypeCode, ShortCode, ManagerCode, Units
     from  mydb1.[dbo].testtablewithTargetColumns
     --clean up
     drop table testtablewithOneColumn,testtablewithTargetColumns

    Thursday, February 25, 2016 4:42 PM
  • Map your columns and main table accordingly in the script below.
    create table #MyTable (col0 varchar(max))
    Insert into #MyTable values('"        ","3800","827","         ","         ","       ","51345","R","S","          10.0000" ')
            CAST('<M>' + REPLACE(col0, ',' , '</M><M>') + '</M>' AS XML) 
            AS col
        FROM  #MyTable 
         REPLACE((col.value('/M[1]', 'varchar(50)')),'"','') As [SalaryCode],
         REPLACE((col.value('/M[2]', 'varchar(50)')),'"','') As [SalaryID],
        REPLACE((col.value('/M[3]', 'varchar(50)')),'"','') As [Department],
    	   REPLACE((col.value('/M[4]', 'varchar(50)')),'"','') As [Region],
         REPLACE((col.value('/M[5]', 'varchar(50)')),'"','') As [Area],
        REPLACE((col.value('/M[6]', 'varchar(50)')),'"','') As [TypeCode],	
    	 REPLACE((col.value('/M[7]', 'varchar(50)')),'"','') As [ShortCode],
         REPLACE((col.value('/M[8]', 'varchar(50)')),'"','') As [ManagerCode],
        REPLACE((col.value('/M[9]', 'varchar(50)')),'"','') As [Units],
    	REPLACE((col.value('/M[10]', 'varchar(50)')),'"','') As [UnknownCol]
    into #t FROM CTE 
    select * from #t
    drop table #t
    drop table #MyTable 


    • Proposed as answer by appsqldev Thursday, February 25, 2016 5:20 PM
    Thursday, February 25, 2016 5:17 PM