如何在 SQL Server 中按字符将字符串拆分为单独的列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21991953/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to Split String by Character into Separate Columns in SQL Server
提问by boyle.matt
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.
我在 SQL Server 中有一个字段，包含部分、城镇和范围信息，每个字段用破折号分隔；例如：18-84-7。我希望将这些信息按每个单位细分，部分为一个字段，乡镇为一个字段，范围为一个字段，例如：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?
字符数不同。每个单位并不总是 2 个字符或 1 个字符，所以我相信最好的方法是用破折号分隔，但我不知道如何做到这一点。有没有办法在 SQL Server 中做到这一点？
Thanks!
谢谢！
采纳答案by BWS
There are probably several different ways to do it, some uglier than others. Here's one:
可能有几种不同的方法来做到这一点，有些比其他方法更丑陋。这是一个：
(Note: dat = the string of characters)
（注：dat = 字符串）
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
回答by Belial09
you could use something like this (posted by @canon)
你可以使用这样的东西（@canon 发布）
CREATE FUNCTION [dbo].[Split]
(
@String varchar(max)
,@Delimiter char
)
RETURNS @Results table
(
Ordinal int
,StringValue varchar(max)
)
as
begin
set @String = isnull(@String,'')
set @Delimiter = isnull(@Delimiter,'')
declare
@TempString varchar(max) = @String
,@Ordinal int = 0
,@CharIndex int = 0
set @CharIndex = charindex(@Delimiter, @TempString)
while @CharIndex != 0 begin
set @Ordinal += 1
insert @Results values
(
@Ordinal
,substring(@TempString, 0, @CharIndex)
)
set @TempString = substring(@TempString, @CharIndex + 1, len(@TempString) - @CharIndex)
set @CharIndex = charindex(@Delimiter, @TempString)
end
if @TempString != '' begin
set @Ordinal += 1
insert @Results values
(
@Ordinal
,@TempString
)
end
return
end
for more take a look at How to split string using delimiter char using T-SQL?
有关更多信息，请查看如何使用 T-SQL 使用分隔符字符拆分字符串？
回答by Amol Shiledar
Please try more reliable code
请尝试更可靠的代码
CREATE BELOW FUNCTION
创建以下功能
CREATE FUNCTION dbo.UFN_SEPARATES_COLUMNS(
@TEXT varchar(8000)
,@COLUMN tinyint
,@SEPARATOR char(1)
)RETURNS varchar(8000)
AS
BEGIN
DECLARE @POS_START int = 1
DECLARE @POS_END int = CHARINDEX(@SEPARATOR, @TEXT, @POS_START)
WHILE (@COLUMN >1 AND @POS_END> 0)
BEGIN
SET @POS_START = @POS_END + 1
SET @POS_END = CHARINDEX(@SEPARATOR, @TEXT, @POS_START)
SET @COLUMN = @COLUMN - 1
END
IF @COLUMN > 1 SET @POS_START = LEN(@TEXT) + 1
IF @POS_END = 0 SET @POS_END = LEN(@TEXT) + 1
RETURN SUBSTRING (@TEXT, @POS_START, @POS_END - @POS_START)
END
GO
AND Then try below code
然后尝试下面的代码
DECLARE @STRING VARCHAR(20) ='1-668-333'
SELECT
dbo.UFN_SEPARATES_COLUMNS(@STRING, 1, '-') AS VALUE1,
dbo.UFN_SEPARATES_COLUMNS(@STRING, 2, '-') AS VALUE2,
dbo.UFN_SEPARATES_COLUMNS(@STRING, 3, '-') AS VALUE3
RESULT
结果
If you need more understanding please go
如果你需要更多的理解，请去
回答by Narottam Goyal
DROP PROCEDURE getName
GO
create proc getName as
begin
select * , substring(name, 1 , CHARINDEX(' ', name)-1) as 'First Name',
SUBSTRING(name, CHARINDEX(' ', name)+1, len(name)) as 'Last Name'
from Employee
order by [Last Name]
end
go
exec getName
