SQL Regex Split Column

Question

I am working on an application which is used to scrape data from an excel spreadsheet and store it into a database. The data is not normalized in excel so the scrape needs to be very flexible. I have recently changed the implementation to be somewhat more generic where before

I was storing the ExcelCellLocation and now have broken it up into ExcelColumn, ExcelRowStart, and ExcelRowEnd.

I am looking for a way to take all of the rows in that table and split up the ExcelCellLocation into the new implementation.

For example:

ExcelCellLocation
F5
AB23

Becomes

ExcelColumn | ExcelRowStart | ExcelRowEnd
F           | 5             | 5
AB          | 23            | 23

The reason for the change is that I am now scraping another spreadsheet in the workbook and the implementation needed to be changed in order to better suit the new spreadsheet while still working for the spreadsheet currently being scraped. The new mappings will look more like this:

ExcelColumn | ExcelRowStart | ExcelRowEnd
BC          | 10            | 150
BC          | 160           | 300

Is there a way to do this with a SQL script that will iterate through each record and make the apropriate changes?

Thanks in advance!

You've included the regex tag, and I can easily imagine a regular expression to transform the F5 and AB23 into F & 5 or AB & 23 respectively, but I am not sure if you've included enough information about the data structure to get an answer - mostly because I'm not sure what your question is!? — Code Jockey
– Code Jockey, Commented Oct 17, 2011 at 16:01

Mikael Eriksson · Accepted Answer · 2011-10-17 15:57:57Z

1

-- Sample data
;with T(ExcelCellLocation) as
(
  select 'F5' union all
  select 'AB23'  
)

-- Query
select  
       left(ExcelCellLocation, patindex('%[0-9]%', ExcelCellLocation)-1) as ExcelColumn,
       stuff(ExcelCellLocation, 1, patindex('%[0-9]%', ExcelCellLocation)-1, '') as RowStart
from T

Result:

ExcelColumn RowStart
----------- -----------
F           5
AB          23

answered Oct 17, 2011 at 15:57

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Code Jockey Over a year ago

I think this answer clarifies a few of my questions concerning my comment on the question itself :D

shuniar Over a year ago

@MikaelEriksson Thanks this works great in the query. how would I use this query to commit it back to the database instead of just return it in a query?

Collectives™ on Stack Overflow

SQL Regex Split Column

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related