I have a column in SQL Server with data that I want to split out delineated by "/" to populate into a new column. I'm wondering if there's a way to do it with SQL Server or if it'd be easier to use Python.

This is a sample of the data now:

This is what I want it to look like:

So far in Python I've been able to pull the data into a CSV file and have sort of figured out how to split it. The problem I'm running into is that once I have it split properly I'll then have to re-write the csv data to SQL Server. I don't know if that's possible.

My python code so far (I'm writing to an empty csv file because as far as I know it's hard to overwrite a column in csv):

import csv
'''getting the col info'''
with open("C:\wamp64\www\SO_PO_Query.csv") as infile:
    reader = csv.reader(infile) # Create a new reader
    next(reader) # Skip the first row
    col = [row[1].split("/")[0] for row in reader]

print(col)

'''
writing 'col' to empty csv file

This works but it writes a space between each value
'''
rows = zip(col)
with open("C:\wamp64\www\empty.csv", "w") as f:
    writer = csv.writer(f)
    for row in rows:
        writer.writerow(row)

CodePudding user response：

T-SQL isn't great at string manipulation, and Python (or any procedural language) is generally better at this. But a single split is fairly simple.

SELECT
  t.Contract_ID,
  Contract_Quote_Number = ISNULL(LEFT(t.Contract_Quote_Number, v.slash - 1), t.Contract_Quote_Number),
  Contract_SO_PO = SUBSTRING(t.Contract_Quote_Number, v.slash   3, LEN(t.Contract_Quote_Number))
FROM YourTable t
CROSS APPLY (VALUES (
    NULLIF(CHARINDEX(' / ', t.Contract_Quote_Number), 0)
) ) v(slash);

• NULLIF ensures the query does not fail if the value is not found

CodePudding user response：

One more method.

SQL

DECLARE @tbl TABLE (Contract_ID int primary key, Contract_Quote_Number VARCHAR(50));
INSERT INTO @tbl (Contract_ID, Contract_Quote_Number) VALUES
(1469, 'COL 386986 / SO 590685'),
(1471, 'COL 387554 / SO 590613'),
(1472, 'COL 387527 / SO 590650'),
(1473, 'COL 387638 / SO 590658');

SELECT Contract_ID
    , Contract_Quote_Number = JSON_VALUE(S,'$[0]')
    , Contract_SO_PO = JSON_VALUE(S,'$[1]')
FROM @tbl
CROSS APPLY (VALUES ('["'   REPLACE(Contract_Quote_Number, ' / ', '","')   '"]')) AS B(S);

Output

 ------------- ----------------------- ---------------- 
| Contract_ID | Contract_Quote_Number | Contract_SO_PO |
 ------------- ----------------------- ---------------- 
|        1469 | COL 386986            | SO 590685      |
|        1471 | COL 387554            | SO 590613      |
|        1472 | COL 387527            | SO 590650      |
|        1473 | COL 387638            | SO 590658      |
 ------------- ----------------------- ----------------