-3

I have the following column in a table.

daily;1;21/03/2015;times;10
daily;1;01/02/2016;times;8
monthly;1;01/01/2016;times;2
weekly;1;21/01/2016;times;4

How can I parse this by the ; delimiter into different columns?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Fearghal
  • 10,569
  • 17
  • 55
  • 97
  • 1
    You can find multiple solution on: http://sqlperformance.com/2012/07/t-sql-queries/split-strings – FLICKER Apr 12 '16 at 16:00
  • 1
    Possible duplicate of [How do I split a string so I can access item x?](http://stackoverflow.com/questions/2647/how-do-i-split-a-string-so-i-can-access-item-x) – Tab Alleman Apr 12 '16 at 16:03

1 Answers1

-1

one way to do it would be to pull it into pandas, delimit by semicolon, and put it back into SQL Server. See below for an example which I tested.

TEST DATA SETUP

enter image description here

CODE

import sqlalchemy as sa
import urllib
import pandas as pd


server = 'yourserver'

read_database = 'db_to_read_data_from'
write_database = 'db_to_write_data_to'

read_tablename = 'table_to_read_from'
write_tablename = 'table_to_write_to'

read_params = urllib.quote_plus("DRIVER={SQL Server};SERVER="+server+";DATABASE="+read_database+";TRUSTED_CONNECTION=Yes")
read_engine = sa.create_engine("mssql+pyodbc:///?odbc_connect=%s" % read_params)

write_params = urllib.quote_plus("DRIVER={SQL Server};SERVER="+server+";DATABASE="+write_database+";TRUSTED_CONNECTION=Yes")
write_engine = sa.create_engine("mssql+pyodbc:///?odbc_connect=%s" % write_params)

#Read from SQL into DF
Table_DF = pd.read_sql(read_tablename, con=read_engine)
#Delimit by semicolon
parsed_DF = Table_DF['string_column'].apply(lambda x: pd.Series(x.split(';')))
#write DF back to SQL
parsed_DF.to_sql(write_tablename,write_engine,if_exists='append')

RESULT enter image description here