CODEWITHSUNDEEP
sqlsql-serverdatabase
user2976138
user2976138

Reputation: 85

Splitting a Column Using a Separator Character in SQL Server

Here is an example row of data stored in this column

'88922774 8785075 9417025'

I am trying to return all 3 separate numbers with the ' ' separator. It's also possible for there to be a 4th column in some cases.

Usually for splitting strings I would have used the following 

PARSENAME(REPLACE([ColumnName], ' ', '.'), 1) 
PARSENAME(REPLACE([ColumnName], ' ', '.'), 2) 
PARSENAME(REPLACE([ColumnName], ' ', '.'), 3)
PARSENAME(REPLACE([ColumnName], ' ', '.'), 4)

However this is not returning any data for this particular table and I do not understand why that is. Because if I execute the following for example I see the expected result:

Select 
    PARSENAME(REPLACE('A Test Name', ' ', '.'), 1),
    PARSENAME(REPLACE('A Test Name', ' ', '.'), 2),
    PARSENAME(REPLACE('A Test Name', ' ', '.'), 3)

I have tried using substring() instead but am only able to return the first and second items from the string, but not the third.

Assistance would be appreciated.

My expected output is this: rextester.com/ACFL85345

However what I actually see is NULL values. I'm using SQL server 2005.

Upvotes: 1

Views: 875

Answers (3)

John Cappelletti
John Cappelletti

Reputation: 81960

If it helps, here is a UDF I used prior to PARSENAME()

Select * from [dbo].[udf-Str-Parse-Row]('88922774 8785075 9417025',' ')

Returns

Pos1        Pos2    Pos3    Pos4    Pos5    Pos6    Pos7    Pos8    Pos9
88922774    8785075 9417025 NULL    NULL    NULL    NULL    NULL    NULL

The UDF

CREATE FUNCTION [dbo].[udf-Str-Parse-Row] (@String varchar(max),@Delimeter varchar(10))
--Usage: Select * from [dbo].[udf-Str-Parse-Row]('Dog,Cat,House,Car',',')
--       Select * from [dbo].[udf-Str-Parse-Row]('John Cappelletti',' ')
--       Select * from [dbo].[udf-Str-Parse-Row]('id26,id46|id658,id967','|')

Returns Table 

As

Return (
    SELECT Pos1 = xDim.value('/x[1]','varchar(250)')
          ,Pos2 = xDim.value('/x[2]','varchar(250)')
          ,Pos3 = xDim.value('/x[3]','varchar(250)')
          ,Pos4 = xDim.value('/x[4]','varchar(250)')
          ,Pos5 = xDim.value('/x[5]','varchar(250)')
          ,Pos6 = xDim.value('/x[6]','varchar(250)')
          ,Pos7 = xDim.value('/x[7]','varchar(250)')
          ,Pos8 = xDim.value('/x[8]','varchar(250)')
          ,Pos9 = xDim.value('/x[9]','varchar(250)')
    FROM (Select Cast('<x>' + Replace(@String,@Delimeter,'</x><x>')+'</x>' as XML) as xDim) A
)

Upvotes: 1

Unnikrishnan R
Unnikrishnan R

Reputation: 5031

Use CHARINDEX for finding the index of your seperator (here ' ') and with the help of SUBSTRING function you can split the string in SQL Server.

DECLARE @val VARCHAR(50)='88922774 8785075 9417025' 

select LEFT(@val, charindex(' ', @val) - 1),
   SUBSTRING(@val, charindex(' ', @val)+1, len(@val) - CHARINDEX(' ', reverse(@val)) - charindex(' ', @val)),
       REVERSE(LEFT(reverse(@val), charindex(' ', reverse(@val)) - 1))

Upvotes: 1

StackUser
StackUser

Reputation: 5398

Try this,

DECLARE @item VARCHAR(MAX) = '88922774 8785075 9417025'

SELECT SUBSTRING(@item, 0, CHARINDEX(' ', @item)) AS col1
    ,SUBSTRING(SUBSTRING(@item, CHARINDEX(' ', @item) + 1, LEN(@ITEM)), 0, CHARINDEX(' ', SUBSTRING(@item, CHARINDEX(' ', @item) + 1, LEN(@ITEM)))) AS col2
    ,REVERSE(SUBSTRING(REVERSE(@ITEM), 0, CHARINDEX(' ', REVERSE(@ITEM)))) AS col3

Upvotes: 0

Related Questions