CODEWITHSUNDEEP
sqlregexsql-server-2008-r2
boyle.matt
boyle.matt

Reputation: 191

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

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!

Upvotes: 8

Views: 68957

Answers (5)

Amol Shiledar
Amol Shiledar

Reputation: 393

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

enter image description here

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

Upvotes: 3

Sara N
Sara N

Reputation: 1189

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.

Upvotes: 1

Narottam Goyal
Narottam Goyal

Reputation: 3652

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

Upvotes: 0

BWS
BWS

Reputation: 3846

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

Upvotes: 8

Belial09
Belial09

Reputation: 694

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?

Upvotes: 2

Related Questions