如何在 SQL Server 中按字符将字符串拆分为单独的列 [英] How to Split String by Character into Separate Columns in SQL Server

问题描述

我在 SQL Server 中有一个字段，包含部分、乡镇和范围信息，每个字段用破折号分隔；例如:18-84-7.我想将这些信息按每个单位分解，部分为一个字段，乡为一个字段，范围为一个字段，例如:18 84 7.

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.

字符数不同.每个单位并不总是 2 个字符或 1 个字符，所以我相信最好的方法是用破折号分隔，但我不知道如何做到这一点.有没有办法在 SQL Server 中做到这一点?

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?

谢谢！

推荐答案

可能有几种不同的方法来做到这一点，有些比其他方法更丑陋.这是一个:

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

(注:dat = 字符串)

(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

这篇关于如何在 SQL Server 中按字符将字符串拆分为单独的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！