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!

BWS · Accepted Answer · 2014-02-24 15:57:22Z

8

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

answered Feb 24, 2014 at 15:57

BWS

3,85620 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sara N Over a year ago

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 Over a year ago

@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?

Joel Coehoorn · Accepted Answer · 2019-01-04 20:28:08Z

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

Community · Accepted Answer · 2017-05-23 11:47:01Z

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?

Community · Accepted Answer · 2017-05-23 12:25:57Z

1

Its better to replace the second section answered by BWS by this one :

select SUBSTRING(dat,CHARINDEX('-', dat) + 1,LEN(dat) - CHARINDEX('-', dat) - CHARINDEX('-', REVERSE(dat)) ) from myTable.

edited May 23, 2017 at 12:25

CommunityBot

11 silver badge

answered Aug 6, 2015 at 1:36

Sara N

1,2295 gold badges18 silver badges48 bronze badges

Comments

Narottam Goyal · Accepted Answer · 2014-10-09 17:34:53Z

0

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

answered Oct 9, 2014 at 17:34

Narottam Goyal

3,6721 gold badge30 silver badges27 bronze badges

Collectives™ on Stack Overflow

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

5 Answers 5

2 Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related