sqlms-access

Separate column value by space and dash

I'm using SQL in MS Access. I have a column in a table that has two types of codes in a single field that follow 4 different types of standards:

     MyColumn
XC120192 - XS38791
XC120192- XS38791
XC120192 -XS38791
XC120192 XS38791

I created two new columns called XC and XS to separate the codes and store them in their respective columns. The expected result would be something like this:

  XC      XS
XC120192  XS38791
XC120192  XS38791
XC120192  XS38791
XC120192  XS38791

How can I do this separation in a way that respects these four different types of separation standards?

Solution

  • As you have fixed length codes:

    Select 
    Left(MyColumn, 8) As XC, 
    Right(MyColumn, 7) As XS
From 
    YourTable