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!

Solution 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

Solution 2

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

https://social.technet.microsoft.com/wiki/contents/articles/26937.t-sql-splitting-a-string-into-multiple-columns.aspx

Solution 3

you could use something like this (posted by @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?

Comments

[Sara N]

It has some issues for the columns which have less length in the first part than the second part. for example :set @text ='mark.sydney.iro' , and then select substring(@text,charindex('.',@text)+1,charindex('.',@text)-‌​1) gets incorrect value

[MattC]

@BWS Sometimes my string has 2 or 3 values to populate (addressline1,addressline2, addessline3). With the sample you provided, if there are only 2 values populated my line3 = line2. How could I prevent that?