How to Split String by Character into Separate Columns in SQL Server
regex, sql, sql-server-2008-r2
Solution
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
Problem
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!