regex - How to Split String by Character into Separate Columns in SQL Server

Question

I have one field in SQL Server containing section, township and range information, each separated by dashes; for example: 18-84-7. I'd like to have this information broken out by each unit, section as one field, township as one field and range as one field, like: 18 84 7.

The number of characters vary. It's not always 2 characters or 1 character per unit, so I believe the best way is to separate by the dashes, but I'm not sure how to do this. Is there a way to do this can be done in SQL Server?

Thanks!

Answer 1

There are probably several different ways to do it, some uglier than others. Here's one:

(Note: dat = the string of characters)

select *,
  substring(dat,1,charindex('-',dat)-1) as Section,
  substring(dat,charindex('-',dat)+1,charindex('-',dat)-1) as TownShip,
  reverse(substring(reverse(dat),0,charindex('-',reverse(dat)))) as myRange
from myTable