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!

score 8 · Accepted Answer · answered Feb 24 '14 at 15:57

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 '14 at 15:57

BWS

3,786
18
25

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 – Sara N Aug 06 '15 at 01:33
@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? – MattC Sep 16 '20 at 14:33

score 3 · Answer 2 · edited Jan 04 '19 at 20:28

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

score 2 · Answer 3 · edited May 23 '17 at 11:47

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?

score 0 · Answer 4 · answered Oct 09 '14 at 17:34

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 09 '14 at 17:34

Narottam Goyal

3,534
27
26

score 0 · Answer 5 · edited May 23 '17 at 12:25

0

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 '17 at 12:25

Community

1
1

answered Aug 06 '15 at 01:36

Sara N

1,079
5
17
45

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

5 Answers5

Linked